Excel Formula / Ignore Blank Cells?

[cpayne1757]

Hi ~

I'm working on a financial formula, trying to calculate a growth rat
for a row of cells. The problem is that not all rows have complete dat
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-zer
entries in all cells, and I *can* manually adjust the formula for th
starting / ending cells in each row--but what a pain(!). As thi
spreadsheet grows larger, or the data sets change over time, it means
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 fo
the blank cells that might exist across a row of data and let th
formula do the calculation?

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

Thanks -

C

 

[Frank Kabel]

Hi
see your other post

 

[Govind]

Are you going to use only two cells i.e. A1 & J1 at a time for computing
the growth rate ?

If so you can use

=IF(ISBLANK(A1),0,((J1/A1)^(1/10)-1))


Regards

Govind.

 

[cpayne1757]

Hi Govind ~

Unfortunately, I want to use all cells in the row that have a non-zero
value, hopefully, skipping any that have blank cells. (=,"

I have sortof figured out how to adjust the exponent section dynamically--
e.g. ^(1/x) by filling 0's in the blank spaces and using
=COUNTIF(A1:J1,">0").
It seems to adjust the exponent when I have less than 10 cell values, but
I'm still struggling with this one.

I'll try to combine your formula with this to see what happens. Thanks -

 

[cpayne1757]

Hi Frank -

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 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 B2. 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. 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 start / end cells and exponent in every row.

Any thoughts?

Thanks-

CP

 

[cpayne1757]

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 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 B2. 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. 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 start / end cells and exponent in every row.

Any thoughts?

Thanks-

CP