Excel formula / How to ignore blank cells?

[Bonsai1757]

Hi ~

I'm working on a financial formula, trying to calculate a growth rate for a
row of cells. The problem is that naturally not all rows have complete data
sets, so the formula picks up blank cells and returns a #DIV/0! error.

The existing formula works fine for those rows that have non-zero entries in
all cells, and I can manually adjust the formula for the starting / ending
cells in each row--but what a pain. As this spreadsheet grows larger, or the
data sets change over time, it means I have to constantly keep adjusting it
for every row of data involved, which is extremely time-consuming.

Here's an short example of what I might have in any given row:

A1 (blank), B1(blank), C1(blank), D1(.10), E1(.25), F1(.72), G1(1.0),
H1(.80), I1(.60), J1(.75)--then in K1 is the growth formula, e.g.
=((J1/A1)^(1/10)-1).

Is there ANY way to create a formula that will dynamically adjust for the
blank cells that might exist across a row of data and let the formula do the
calculation?

I'm new to advanced Excel topics, but any ideas would be MOST appreciated(!).

Thanks -

CP

 

[Frank Kabel]

Hi
you may also post your current used formula

 

[Arvi Laanemets]

Hi

=IF(A1="","",((J1/A1)^(1/10)-1))
or
=IF(OR(A1="",J1=""),"",((J1/A1)^(1/10)-1))

 

[cpayne1757]

Hi Arvi ~

Ok, let me modify this a bit--

It looks like the best approach for this situation is to just start at cell
A2 and check each data cell in sequence to see if there are blanks, then
ignore them and do the calculation using a modified exponent value (e.g.
^(1/x).

I was able to get the exponent to adjust when there are less than 10 full
data cells(A2:J2) by way of =COUNTIF(A2:J2,">0") and using an extra column
for the value(K2).

With an IF statement, I can get the formula to work if the first cell A2
happens to be blank (e.g. it moves to B2, then does the growth formula with
an adjusted exponent). The problem is getting it work beyond cell A2. I'm
not so experienced with Excel, so maybe I'm doing this the hard way.

Here's what I have now in (L2):

=IF(A2="",((J2/B2)^(1/K2)-1),((J2/A2)^(1/K2)-1))

I've tried to nest the IF statement to continue down the row for 7 cells,
but I can't get it to work beyond the first blank cell (A2). I understand
that the IF statement can only nest for 7 steps, but it would be better than
nothing--at least I wouldn't have to go in an manually adjust the beginning /
ending data cells and exponent for every row.

Any thoughts?

Thanks-

CP

 

[Arvi Laanemets]

Hi

How must the formula work, when there are several cells in range A2:J2 with
value >0 ? Is only the first one used, or is the expression calculated for
every cell with value>0 and then summed ?

 

[Arvi Laanemets]

And for case every non-zero numeric value is raised, and result summed:
=IF(AND(A2>0;A2<"a");A2^(1/10)-1;0)+IF(AND(B2>0;B2<"a");B2^(1/10)-1;0)+IF(AN
D(C2>0;C2<"a");C2^(1/10)-1;0)+IF(AND(D2>0;D2<"a");D2^(1/10)-1;0)+IF(AND(E2>0
;E2<"a");E2^(1/10)-1;0)+IF(AND(F2>0;F2<"a");F2^(1/10)-1;0)+IF(AND(G2>0;G2<"a
");G2^(1/10)-1;0)+IF(AND(H2>0;H2<"a");H2^(1/10)-1;0)+IF(AND(I2>0;I2<"a");I2^
(1/10)-1;0)+IF(AND(J2>0;J2<"a");J2^(1/10)-1;0)

(relpace delimiters - your windows uses different ones)

 

[Arvi Laanemets]

Hi

Here is the one for case the first value>0 in row is used:
=(IF(AND(A2>0;A2<"a");A2;0)+(SUM(A2)=0)*IF(AND(B2>0;B2<"a");B2;0)+(SUM(A2:B2
)=0)*IF(AND(C2>0;C2<"a");C2;0)+(SUM(A2:C2)=0)*IF(AND(D2>0;D2<"a");D2;0)+(SUM
(A22)=0)*IF(AND(E2>0;E2<"a");E2;0)+(SUM(A2:E2)=0)*IF(AND(F2>0;F2<"a");F2;0
)+(SUM(A2:F2)=0)*IF(AND(G2>0;G2<"a");G2;0)+(SUM(A2:G2)=0)*IF(AND(H2>0;H2<"a"
);H2;0)+(SUM(A2:H2)=0)*IF(AND(I2>0;I2<"a");I2;0)+(SUM(A2:I2)=0)*IF(AND(J2>0;
J2<"a");J2;0))*(^(1/10)-1)

(replace delimiters in formula - your windows uses different ones)

 

[cpayne1757]

Hi ~

Ok, let me try to clairfy this a bit further. I am doing a CAG
(compound annual growth rate) calculation, which is a financia
equation to measure growth between the starting / ending periods, wit
the exponent adjusted to match the number of periods involved--it's no
summing the amounts, it's more of a smoothed average calculation.

Right now I have a row of numbers across cells (A2:JA), e.g.:

(A1:J1)
1990 1991 1992 1993 1994 1995 1996 1997 1998 1999
(A2:J2) (K2) (L2)
1 2 3 4 5 6 7 8 9 10 10 0.258


In L2 I want to determine the growth rate for this period, e.g
(J2/A2)^(1/10). If there are any blank cells starting at A2, I want th
formula to ignore them and adjust the forumula accordingly.

In most cases, I have data cells for all ten periods, but in some case
I have less, so this means I have to adjust the start / end period an
also the exponent in the formula.

This is a HUGE problem I discovered after the fact, because th
spreadsheet is large, and going in by hand and adjusting every formul
to match the number of periods is tedious to say the least. Goin
forward, the data will change--so it will always be a hassle unless
figure this out.

Anyway, since I am always using 10 years of data, I figured out how t
adjust the exponent dynamically by using =10-COUNTBLANK(A2:J2) i
column K2 and plugging this into the formula, e.g. ^(1/K2). If I g
back and delete any cell value between A2:J2, the counter (K2
automatically reflects the total number of periods remaining.

It would seem simple enough, but I am still stuck on the base forumula
trying to get the blank cells ignored. Basically, I think what it shoul
do is start in the leftmost position, e.g. A2, check if it's blank an
if so, move on to B2. If B2 has a value, to do the calculation with th
adjusted exponent, e.g. ((J2/B2)^(1/K2)-1)).

Right now, I have this formula in column L2:

=IF(A2="",((J2/B2)^(1/K2)-1),IF(B2="",((J2/C2)^(1/K2)-1)
((J2/A2)^(1/K2)-1)))

It seems to do the correct calculation if I delete the cell value fro
A2, but if I also delete B2, I get a #DIV/0! error. I had tried nestin
cells A2:G2 (7 max, right?), but still had the error. So I figured tha
I should back up and start over.

I will still have to monitor the data for any blank cells between th
start / end period cells, or the math formula won't work correctly, bu
that's do-able. If I can get the formula to ignore the blank cell
starting at A2 and moving to the right across to J2, that will be a bi
help.

There might be an easier way to do this, I'm not sure. Probably, ther
is something more elegant out there--who knows.

If you have any thoughts on how I can rework / revise this formula
please advise. I'll be happy to give it a try.

Thanks -

C

 

[Arvi Laanemets]

Hi

Did you try my formulas at all? The first one does exactly what you are
searching for - except like your own example it doesn't involve the growth
value for ending period (maybe you write down the calculation with your
example figures here).

Right now I have a row of numbers across cells (A2:JA), e.g.:

(A1:J1)
1990 1991 1992 1993 1994 1995 1996 1997 1998 1999
(A2:J2) (K2) (L2)
1 2 3 4 5 6 7 8 9 10 10 0.258
....

Right now, I have this formula in column L2:

=IF(A2="",((J2/B2)^(1/K2)-1),IF(B2="",((J2/C2)^(1/K2)-1),
((J2/A2)^(1/K2)-1)))

=(IF(AND(A2>0,A2<"a"),A2,0)+(SUM(A2)=0)*IF(AND(B2>0,B2<"a"),B2,0)+(SUM(A2:B2
)=0)*IF(AND(C2>0,C2<"a"),C2,0)+(SUM(A2:C2)=0)*IF(AND(D2>0,D2<"a"),D2,0)+(SUM
(A22)=0)*IF(AND(E2>0,E2<"a"),E2,0)+(SUM(A2:E2)=0)*IF(AND(F2>0,F2<"a"),F2,0
)+(SUM(A2:F2)=0)*IF(AND(G2>0,G2<"a"),G2,0)+(SUM(A2:G2)=0)*IF(AND(H2>0,H2<"a"
),H2,0)+(SUM(A2:H2)=0)*IF(AND(I2>0,I2<"a"),I2,0)+(SUM(A2:I2)=0)*IF(AND(J2>0,
J2<"a"),J2;0))^(1/K2)-1

When you are sure there never will be any text entries in range A2:J2, then
you can omit both checks for <"a" and >0:
=(A2*1+(SUM(A2)=0)*B2+(SUM(A2:B2)=0)*C2+(SUM(A2:C2)=0)*D2+(SUM(A22)=0)*E2+
(SUM(A2:E2)=0)*F2+(SUM(A2:F2)=0)*G2<+(SUM(A2:G2)=0)*H2+(SUM(A2:H2)=0)*I2+(SU
M(A2:I2)=0)*J2)^(1/K2)-1



Arvi Laanemets