To meet the first formula logic requirement and then apply the next formula

Princess, Rachel 20 Reputation points
2026-07-02T06:13:42.6066667+00:00

for a Quarterly data value, the monthly value is updated. Eg. For Q1, only after Mar data is entered the IF logic formula applied should compute and provide the value as output.

However even if i put value for Jan, the output value is provided for Q1 section

Which logic formula to be used.

Microsoft 365 and Office | Excel | Other | Other
0 comments

Answer accepted by question author

Mite Suo 5,195 Reputation points Independent Advisor
2026-07-02T11:35:35.47+00:00

The #VALUE! error is likely happening because Excel is trying to calculate before all monthly values are available, or because one of the referenced cells contains text instead of a number.

The first part of your formula should check whether the required monthly cells are complete. Only after that should Excel run the calculation.

If your three monthly cells are H77, I77, and J77, you can use this:

=IF(COUNTBLANK('Metric Data Inputs'!H77:J77)>0,"N/A",IF(SUM('Metric Data Inputs'!H76:J76)=0,"N/A",SUM('Metric Data Inputs'!H77:J77)/SUM('Metric Data Inputs'!H76:J76)))

This will return N/A while any of the monthly cells are still blank. Once all three months have values, the formula will calculate the result.

Also, your formula includes J77, but you mentioned that H = June, I = July, and K = August. If August is actually in column K, then you should use H, I, and K instead of H, I, and J.

In that case, use this version:

=IF(COUNTBLANK('Metric Data Inputs'!H77)+COUNTBLANK('Metric Data Inputs'!I77)+COUNTBLANK('Metric Data Inputs'!K77)>0,"N/A",IF(SUM('Metric Data Inputs'!H76,'Metric Data Inputs'!I76,'Metric Data Inputs'!K76)=0,"N/A",SUM('Metric Data Inputs'!H77,'Metric Data Inputs'!I77,'Metric Data Inputs'!K77)/SUM('Metric Data Inputs'!H76,'Metric Data Inputs'!I76,'Metric Data Inputs'!K76)))

The main idea is to check for blank monthly values first, then check whether the denominator is zero, and only then perform the calculation.

Was this answer helpful?

1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. riny 20,950 Reputation points Volunteer Moderator
    2026-07-02T15:01:08.8733333+00:00

    @Princess, Rachel

    Can't be sure what you are trying to achieve without seeing your file, but it seems you only want to calculate something when all three months in the quarter on row 77 contain a value. Otherwise you want "N/A".

    If that's correct then this should do just that:

    =IF(COUNT('Metric Data Inputs'!H77:J77)=3,SUM('Metric Data Inputs'!H77:J77)/SUM('Metric Data Inputs'!H76:J76),"N/A")

    Was this answer helpful?

    0 comments
  2. Mite Suo 5,195 Reputation points Independent Advisor
    2026-07-02T06:55:04.8366667+00:00

    Hello, I am Mite, and I am here to help.

    You need to add a condition that checks whether the quarter is complete before running your main formula.

    For example, if your Q1 monthly values are in `B2:D2 for Jan, Feb, and Mar, you can use:

    =IF(COUNTBLANK(B2:D2)>0,"",your_formula)

    For example, if your current formula is:

    =IF(SUM(B2:D2)>100,"OK","Not OK")

    change it to:

    =IF(COUNTBLANK(B2:D2)>0,"",IF(SUM(B2:D2)>100,"OK","Not OK"))

    This means Excel will return a blank result until all three months in the quarter have values. Once Jan, Feb, and Mar are filled in, the IF logic will run and return the quarterly result.

    If you only want the formula to run after March has a value, you can use:

    =IF(D2="","",your_formula)

    However, checking all three months with "COUNTBLANK" is usually safer if the quarter should only calculate after all monthly data is entered.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.