If Then Help

Your question is quite confusing to understand. I don't know what you mean by "let the formula run". What exactly are you wanting to see that you are not seeing and where...what cells? Some examples of what you expect would be appreciated.

Maybe you should be a little more explicit with the title of the thread giving what you are looking for instead of "help". Your last sentence has most of what could be the basis of a better title. This could attract more action to the question.

Are you sure that is what you want. Do you want the average per day (1760/5=352) or something else???? Basically - why is 352 the answer?

Maybe this approach will work for you. The areas in yellow are what I did.

I added a helper column to calculate the week.

Formula:

Please Login or Register  to view this content.

Then for the averages:

Formula:

Please Login or Register  to view this content.

I don't understand at all why you are doing what you are doing. The first example calculates averages so why not use the AVERAGEIF function to sum and divide by the number of values to get the average?

AVERAGEIF is using the week number and calculating the correct average for the week without having to count and sum.

What you call the zero error are real zeros. The IFERROR enters a zero if the formula results in an error. Select the zeros produced for the future weeks and you will see that Excel (in the status bar at the bottom) counts the values and sums them. If this was just a visual presentation or text, that would not be the case.

I don't even think it's averageif. It's an average. you can use either of the two methods shown here. The one in green shading is fully automatic - it just needs a table of 5x5 days. the one in pink is closer to your original. If you don't like the green solution, dlete the additonal few rows and maually reset the last pink formula to suit.

I don't understand why you are doing what you are doing. The first example was evidently calculating averages. Excel has functions for averaging (AVERAGE, AVERAGEIF etc).

The week numbers that I showed how to calculate are used with the AVERAGEIF to calculate the average for the week without having to sum, count and divide.

The zeros produced by the IFERROR for future weeks are real zeros and not a representations of zeros. To prove that, select them and in the status bar you will see that Excel has not only counted them but summed them as well...treating them as the numbers they are.

It happens to me all too often!!

Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.

Overthinking is as common as the common cold! Easy to get trapped into...we have all been there.

I believe what you're looking for is an alteration to your formula, so that when the space is BLANK, to show $0. but when it's actually zero, to run the formula, correct?
If that's the case, then you're wanting the following:

Please Login or Register  to view this content.

That's saying if B3 is not blank (not the same as 0!), do this calculation; otherwise, result 0.