Excel formula question

I am trying to create a formula that does not recognize a missing value. The formula is as follows:

=(C15+D15+E15+F15+G15)/5*B15

The question I have is how do I change the “5” in the formula so that if I am missing a data point in any of the cells listed it does not use 5 but uses only the cells with data in them? Really what I am looking for is a formula that only uses cells that have data so the answer is not being reduced more than it should be since it only uses cells with data…

HELP…

The simplest solution is to use the AVERAGE() function, which will automatically disregard empty and non-numerical cells in a range:

=AVERAGE(C15:G15)*B15

For more elaborate conditional sums or averages, have a look at the SUMIF() and COUNTIF() functions.

Welcome to the forums! :slight_smile:

Thanks but to complete the formula and to get the correct answer I need to divide the sum (C15:G15) by the number of entries posted (cells with data in them in that range) and then multiple it by B15. I hope I am making myself clear…

Thanks,

[QUOTE=JonSeeger;2485289]Thanks but to complete the formula and to get the correct answer I need to divide the sum (C15:G15) by the number of entries posted (cells with data in them in that range) and then multiple it by B15. I hope I am making myself clear… [/QUOTE] If I understand you correctly, that is exactly what my suggested formula will accomplish. Why don’t you try it and see for yourself?

You are absolutely correct… Thanks for help. I was having a brain meltdown and wasn’t thinking clearly.

Thanks for the help and showing me the light.

You’re welcome! :slight_smile:

Ok now that you helped me with that, can you help me with one more request? I now have that formula working correctly but now is shows the #DIV/0 message in the cell since the spreadsheet is blank. How do i get that from now showing and either show a zero or nothing at all?

[QUOTE=JonSeeger;2485298]I now have that formula working correctly but now is shows the #DIV/0 message in the cell since the spreadsheet is blank. How do i get that from now showing and either show a zero or nothing at all?[/QUOTE]Showing either the value or an empty string:

=IF(ISNUMBER(AVERAGE(C15:G15));AVERAGE(C15:G15)*B15;"")

Showing either the value or zero:

=IF(ISNUMBER(AVERAGE(C15:G15));AVERAGE(C15:G15)*B15;0)

Depending on your locale (country), you may have to use commas instead of semicolons - I’m not sure.

Thread moved since this isn’t really a hardware problem :wink:

I was kinda proud to have this thread in the HW forum, DrageMester did such a nice job of answering the question :cool: :smiley: But I’ll let you software people have it :disagree: :iagree: