Software

Ask something
Question Locked

Excel argument/formula question

0 helpful
6 replies

by rosstamon

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Let's say I have the POTENTIAL to have data in cellS A1, B1, C1 and D1. In cell E1 I want to add all four cells together. That's easy. Now in cell F1 I want to say take the total from E1 and divide it by the number of cells that have data. One time all four cells may have data. Another time maybe only two cells have data. Does anyone know a formula that will say, I see data in cells A1, B1 and C1 so divide E1 by 3. And then the next time if all four cells have data it will automatically divide by 4, or is this not a possible argument / formula in Excel.
Many thanks in advance.

6 replies

by churdoo

=sum(a1:d1)/Count(a1:d1) should do the trick, or to avoid a potential divide by zero error,
=iff(count(a1:d1)>0,sum(a1:d1)/Count(a1:d1),null)

by rdk55

In cell F1 enter "=AVERAGE(A1:D1)".
This will do exactly what your looking for no matter how many entries you have.

by rosstamon

Thank you. That is an excellent way to do it. I never even thought to enquire whether excel did an average. I'm actually not bad using excel, but don't know all of the formula possibilities. How did you learn them. Is there a good web site or other resource. I appreciate your response.

by rdk55

Glad I could help.

Actually I'm fairly self-taught in Excel myself but if I ever do run into problems, my number one resource is MrExcel.com. It's a great site with a lot of experts available to answer the tough questions.

by absolutely

Similarly with if(and( ...

Excel doesn't actually use the term "case", but by the time you type the second '(', the rest will be filled for you. Average is best for the question you asked, but this might also help.

This discussion is closed to new replies.