[Solved] Excel Formula help

D

daveb1967b

New member
I have a table with columns where a value is either not completed yet based on another formula (shows as a negative value (-45644.0) or is a positive value (number of days since completed) in that column. I need a formula that looks at the positive values and compares it to a target (48 days for example). I want to know of all the positive values completed in those column cells, what percentage were completed on or before the target value.
 
Hello,

You can use the following formula to calculate the percentage of values completed on or before the target value (e.g., 48 days).

Assuming your positive values (number of days since completion) are in column A (starting from A2), and you want to compare it to a target of 48 days, you can use this formula:

=COUNTIF(A2:A100, "<=48") / COUNTIF(A2:A100, ">0")

Explanation:
  • COUNTIF(A2:A100, "<=48"): Counts the number of cells with values less than or equal to 48 (days completed on or before the target).
  • COUNTIF(A2:A100, ">0"): Counts the number of cells with positive values (completed tasks).
  • The division gives you the percentage of tasks completed on or before the target.
Multiply the result by 100 if you want the percentage as a whole number.
 
shamimarita said:
Hello,

You can use the following formula to calculate the percentage of values completed on or before the target value (e.g., 48 days).

Assuming your positive values (number of days since completion) are in column A (starting from A2), and you want to compare it to a target of 48 days, you can use this formula:

=COUNTIF(A2:A100, "<=48") / COUNTIF(A2:A100, ">0")

Explanation:
  • COUNTIF(A2:A100, "<=48"): Counts the number of cells with values less than or equal to 48 (days completed on or before the target).
  • COUNTIF(A2:A100, ">0"): Counts the number of cells with positive values (completed tasks).
  • The division gives you the percentage of tasks completed on or before the target.
Multiply the result by 100 if you want the percentage as a whole number.
Click to expand...
This almost works. But I am pretty sure I need the 1st line to be anything between 0 and 48, and the second one to be anything > 48. I can take care of the second one but how do i alter your first part of the formula to say >=0 to <=48?
 
Hello,

To modify the formula to count values between 0 and 48 (inclusive), you can use the following formula:

=COUNTIFS(A2:A100, ">=0", A2:A100, "<=48")
  • COUNTIFS allows you to apply multiple conditions.
  • A2:A100, "&gt;=0" ensures that the values are greater than or equal to 0.
  • A2:A100, "&lt;=48" ensures that the values are less than or equal to 48.
This will count all values between 0 and 48 (inclusive).

Now, you can use this updated formula as the numerator for your percentage calculation:
=COUNTIFS(A2:A100, ">=0", A2:A100, "<=48") / COUNTIF(A2:A100, ">0")
This calculates the percentage of completed tasks that were finished between 0 and 48 days. Multiply by 100 if you want the percentage as a whole number.
 
daveb1967b said:
This almost works. But I am pretty sure I need the 1st line to be anything between 0 and 48, and the second one to be anything > 48. I can take care of the second one but how do i alter your first part of the formula to say >=0 to <=48?
Click to expand...

shamimarita said:
Hello,

To modify the formula to count values between 0 and 48 (inclusive), you can use the following formula:

=COUNTIFS(A2:A100, ">=0", A2:A100, "<=48")
  • COUNTIFS allows you to apply multiple conditions.
  • A2:A100, "&gt;=0" ensures that the values are greater than or equal to 0.
  • A2:A100, "&lt;=48" ensures that the values are less than or equal to 48.
This will count all values between 0 and 48 (inclusive).

Now, you can use this updated formula as the numerator for your percentage calculation:
=COUNTIFS(A2:A100, ">=0", A2:A100, "<=48") / COUNTIF(A2:A100, ">0")
This calculates the percentage of completed tasks that were finished between 0 and 48 days. Multiply by 100 if you want the percentage as a whole number.
Click to expand...
Thanks ! This looks perfect to me and I tried to utilize it but it is not calculating correctly. driving me nuts. for example - I have a column it is looking at that has 15 total bottles that fit the criteria for the second half, and 12 that are between a and 48, the calc shows 97% rather than 80%. have messed around with it but no idea why it is not reporting accurately? I really appreciate your help and I will continue to look but wanted to let ya know this weird situation. thanks again.
 
If the calculation isn't returning the correct percentage, it suggests there might be an issue with either the formula range or how the conditions are interpreted. Here are some troubleshooting steps to ensure accuracy:

1. Check the Range in the Formula

Verify that the range in the COUNTIFS formula (A2:A100 in my example) matches the actual range of your data. If your data extends beyond row 100, update the range.

=COUNTIFS(A2:A100, ">=0", A2:A100, "<=48") / COUNTIF(A2:A100, ">0")

2. Ensure the Data Contains Only Numbers

Check the column for non-numeric values, empty cells, or hidden characters (e.g., spaces). Non-numeric values could be causing unexpected results. Use ISNUMBER to validate the data:

=SUMPRODUCT(--ISNUMBER(A2:A100))

If the count doesn't match the expected total (15 in your example), clean the data.

3. Validate Positive Values

Ensure the COUNTIF(A2:A100, ">0") part accurately reflects all positive values. Check if any unexpected formatting or rounding errors are impacting the results.

4. Manually Verify the Results

Manually count the values between 0 and 48 and the total positive values. Compare them with the formula output to pinpoint discrepancies.

5. Revised Formula for Debugging

To isolate issues, you can temporarily calculate the numerator and denominator separately:

  • Count values between 0 and 48:
=COUNTIFS(A2:A100, ">=0", A2:A100, "<=48")

  • Count all positive values:
=COUNTIF(A2:A100, ">0")

  • Use these intermediate results to verify the percentage calculation:
=Numerator / Denominator

6. Formatting Errors

Ensure the formula cell isn't formatted as a percentage with incorrect decimal places. For example, a result of 0.8 displayed as 97% could indicate the cell is mistakenly set to show 3 decimal places (e.g., 80.0%).
 
shamimarita said:
If the calculation isn't returning the correct percentage, it suggests there might be an issue with either the formula range or how the conditions are interpreted. Here are some troubleshooting steps to ensure accuracy:

1. Check the Range in the Formula

Verify that the range in the COUNTIFS formula (A2:A100 in my example) matches the actual range of your data. If your data extends beyond row 100, update the range.

=COUNTIFS(A2:A100, ">=0", A2:A100, "<=48") / COUNTIF(A2:A100, ">0")

2. Ensure the Data Contains Only Numbers

Check the column for non-numeric values, empty cells, or hidden characters (e.g., spaces). Non-numeric values could be causing unexpected results. Use ISNUMBER to validate the data:

=SUMPRODUCT(--ISNUMBER(A2:A100))

If the count doesn't match the expected total (15 in your example), clean the data.

3. Validate Positive Values

Ensure the COUNTIF(A2:A100, ">0") part accurately reflects all positive values. Check if any unexpected formatting or rounding errors are impacting the results.

4. Manually Verify the Results

Manually count the values between 0 and 48 and the total positive values. Compare them with the formula output to pinpoint discrepancies.

5. Revised Formula for Debugging

To isolate issues, you can temporarily calculate the numerator and denominator separately:

  • Count values between 0 and 48:
=COUNTIFS(A2:A100, ">=0", A2:A100, "<=48")

  • Count all positive values:
=COUNTIF(A2:A100, ">0")

  • Use these intermediate results to verify the percentage calculation:
=Numerator / Denominator

6. Formatting Errors

Ensure the formula cell isn't formatted as a percentage with incorrect decimal places. For example, a result of 0.8 displayed as 97% could indicate the cell is mistakenly set to show 3 decimal places (e.g., 80.0%).
Click to expand...
TY! - I will check this
 
I’ve had a similar challenge before, where I needed to compare values against a target and calculate the percentage of tasks that were completed on time. For your situation, you can use an IF statement combined with COUNTIF or COUNTIFS to count how many of the positive values are less than or equal to the target value.
 
You must log in or register to reply here.

Online statistics

Members online
0
Guests online
8
Total visitors
8
Totals may include hidden visitors.

Forum statistics

Threads
400
Messages
1,766
Members
815
Latest member
hendik

Share this page

Back
Top