Solved Cumulative Excel Formula - Skip Blank Cells

March 28, 2016 at 23:08:43
Specs: Windows Server 2012 R2
Hi,

I am trying to find a way to have the Excel formula skip blank cells but if a cell has data in it to add it to the formula.

For example rainfall percentage over a year.

I have columns to put in the rainfall in mms and at the end I would like a cell for the percentage of the year to date rainfall. This is to be compared against a standard amount of mms.

At the moment my formula looks like this:

=(SUM(E2+G2+I2+K2+M2+O2+Q2+S2+U2+W2+Y2+AA2+AC2))/(D2*2)

But as the months go on I have to keep changing what "D2" is multiplied against, and would like it to be done via the formula so all I need to do is add an amount into the next months rainfall cell and have the percent update.

Please let me know if any more information is needed.


See More: Cumulative Excel Formula - Skip Blank Cells

Report •

✔ Best Answer
March 30, 2016 at 17:14:49
OK, I made an error, but first I want to point out something about your SUM formula.

These 2 formulas are equivalent:

=C2+E2+G2
=SUM(C2,E2,G2)

This (your) formula is redundant, Excel is smart enough to ignore the fact that you are telling it to SUM the same numbers that you are manually SUMing.

=SUM(C2+E2+G2)

In addition, if D2 and F2 are blank, then this formula will work just as well:

=SUM(C2:G2)

OK, as for my error, I posted too quickly and should have checked it more carefully.

Try:

=SUM(C2,E2,G2)/(A2*COUNT(C2,E2,G2))

or, if D2 & F2 are empty,

=SUM(C2:G2)/(A2*COUNT(C2:G2))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
March 29, 2016 at 04:38:56
First, you don't need the parenthesis around the SUM function.

Second, your subject line includes the words "Skip Blank Cells". I'm assuming columns F, H, J, etc. are blank, which means they they won't impact the SUM.

Why not just use this?

=SUM(E2:XFD2)/(D2*2)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
March 29, 2016 at 22:52:08
Hi DerbyDad03,

Thankyou for your response.

What I need is for the last calculation in the formula to take into account when I put in a new value.

For example at the moment it is (D2*2) because I have 2 months worth of rainfall filled out, I would like it to to change to (D2*3) when I fill out the 3 months worth, and (D2*4) when I fill in the 4th month, etc.

So it is not really skipping blank cells more so registering when something is put into a cell to update the last calculation in the formula.

Hopefully this means sense.


Report •

#3
March 30, 2016 at 04:28:41
Look up the COUNT function.

The COUNT function will count the number of cells in a range that contain numbers.

Something like:

SUM(your_range)/(D2*COUNT(your_range))

If that doesn't work you'll need to be more specific because I am still not 100% clear on what your exact process is.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
March 30, 2016 at 05:15:10
Hi DerbyDad03,

Thankyou for your response.

What it looks like is roughly this

Standard Month 1 Month 2 Month 3 Year to Date Percentage
11 3 5 2 24%

So if I have data under

Month 1 the formula for the Year to Date Percentage would be =SUM(B2+C2+E2)/A2
Month 1 & 2 the formula for the Year to Date Percentage would be =(SUM(C2+E2+F2))/(A2*2)
Month 1, 2 & 3 the formula for the Year to Date Percentage would be =(SUM(C2+E2+F2))/(A2*3)

I need to find a way that I don't need to change the "/(A2*3)" part every time I put data into cell.

Is there a way to count, if data in 3 of the listed cells so the formula is *3, and if there is data in 4 of the listed cells the formula is *4 and so on.

Hopefully this makes a bit more sense.


Report •

#5
March 30, 2016 at 05:46:28
Are you looking for the AVERAGE() rain fall?

If that is what your trying to do then simply use the AVERAGE() function:

=AVERAGE(E2:XFD2)

One caveat when using the AVERAGE() function:

1) If the cell contains zero 0, the cell will be included in the average calculation.

So don't use a Zero as a place holder, just leave the cell blank.

MIKE

http://www.skeptic.com/


Report •

#6
March 30, 2016 at 06:17:15
Hi Mike,

I need it to be in reference to a standard that is in one cell.

A2 has the standard rainfall that the calculation references to.
C2 has month 1's rainfall
E2 has month 2's rainfall
F2 has month 3's rainfall

I need the calculation to work out if I have data in the listed cells and alter itself appropriately.

For example,

if I only have data in C2 then the calculation will just be /A2,

but if I have data in E2 as well as C2 then the calculation has to double itself,

and if I have data in F2 as well as E2 & C2 then the calculation has to triple itself.


Report •

#7
March 30, 2016 at 06:49:07
First, I am going to repeat what I said earlier, with emphasis this time:

You don't need the parenthesis around the SUM function.

=(SUM(C2+E2+F2))/(A2*2) can be written as =SUM(C2+E2+F2)/(A2*2)

Your formula will be less cluttered without the extra parenthesis.

Second, have you tried my COUNT function suggestion?

=SUM(C2+E2+F2)/(A2*COUNT(C2+E2+F2))

When there is data in C2 only, COUNT(C2+E2+F2) will equal 1.
When there is data in C2 and E2, COUNT(C2+E2+F2) will equal 2.
When there is data in C2, E2 and F2, COUNT(C2+E2+F2) will equal 3.

Isn't that what you want?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#8
March 30, 2016 at 16:43:11
Hi DerbyDad03,

Thankyou for your response.

I have tried your formula but I'm not sure where it goes wrong.

With the formula of =SUM(C2+E2+G2)/(A2*COUNT(C2+E2+G2)) I get the percentage of 27% if I have data in just cell C2
With the formula of =SUM(C2+E2+G2)/A2 I get the percentage of 27% if I have data in just cell C2

With the formula of =SUM(C2+E2+G2)/(A2*COUNT(C2+E2+G2)) I get the percentage of 45% if I have data in cell C2 & E2
With the formula of =SUM(C2+E2+G2)/(A2*2) I get the percentage of 23% if I have data in cell C2 & E2

With the formula of =SUM(C2+E2+G2)/(A2*COUNT(C2+E2+G2)) I get the percentage of 91% if I have data in cell C2, E2 &G2
With the formula of =SUM(C2+E2+G2)/(A2*3) I get the percentage of 30% if I have data in cell C2, E2 &G2


Report •

#9
March 30, 2016 at 17:14:49
✔ Best Answer
OK, I made an error, but first I want to point out something about your SUM formula.

These 2 formulas are equivalent:

=C2+E2+G2
=SUM(C2,E2,G2)

This (your) formula is redundant, Excel is smart enough to ignore the fact that you are telling it to SUM the same numbers that you are manually SUMing.

=SUM(C2+E2+G2)

In addition, if D2 and F2 are blank, then this formula will work just as well:

=SUM(C2:G2)

OK, as for my error, I posted too quickly and should have checked it more carefully.

Try:

=SUM(C2,E2,G2)/(A2*COUNT(C2,E2,G2))

or, if D2 & F2 are empty,

=SUM(C2:G2)/(A2*COUNT(C2:G2))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
March 30, 2016 at 17:15:36
DO NOT use the Plus sign with the COUNT() and SUM() functions, it only confuses the calculations.

Use the Range syntax like:

=SUM(C2:G2)/(A2*COUNT(C2:G2))

See if that gives you the answer you expect.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#11
March 30, 2016 at 17:24:17
I'll take the blame for the plus signs in the COUNT function. (My #7)

I was in a hurry and didn't check my work.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#12
March 30, 2016 at 17:58:08
Hi DerbyDad03 & Mike,

Thankyou for your responses.

I believe that DerbyDad03's #9 has solved my issue.

Thankyou both so much for your assistance!


Report •

Ask Question