Excel formula question

Software & Apps
#1

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…

#2

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:

#3

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,

#4

[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?

#5

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.

#6

You’re welcome! :slight_smile:

#7

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?

#8

[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.

#9

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

#10

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: