# Excel Formula / How Can I Ignore Blank Cells In Row Formula?

Discussion in 'Business Applications' started by cpayne1757, Oct 13, 2004.

Not open for further replies.

Joined:
Oct 13, 2004
Messages:
5
Hi ~

I'm working on a financial formula, trying to calculate a growth rate for a row of cells. The problem is that 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

2. ### Rollin_Again

Joined:
Sep 4, 2003
Messages:
4,912
Can you explain your formula in a little more detail? It sounds like what you want to do is possible but I'm having a problem understanding what the formula is supposed to do and where the values in your formula come from.

Rollin

3. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,891
First Name:
Wayne
so you want to change the the formula from this
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).

to something which looks at the cagr (if thats what it is) and change the number of years??? to take the average for year 1,2,3
OR
are you looking for =((J1/D1)^(1/7)-1).

Joined:
Oct 13, 2004
Messages:
5
Hi ~

Ok, sure--let me try to clairfy this a bit further (yes, I am doing CAGR calculation):

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 the formula to ignore them and adjust the forumula accordingly.

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

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

Anyway, I figured out how to adjust the exponent dynamically by using =COUNTBLANK(A2:J2) in column K2 and plugging this into the formula, e.g. ^(1/K2). If I go back and delete any cell value between A2:J2, the counter (K2) 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 should do is start in the leftmost position, e.g. A2, check if it's blank and if so, move on to B2. If B2 has a value, to do the calculation with the 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 from A2, but if I also delete B2, I get a #DIV/0! error. I had tried nesting cells A2:G2 (7 max, right?), but still had the error. So I figured that I should back up and start over.

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

There might be an easier way to do this, I'm not sure. Probably, there 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 -

CP

Joined:
Oct 13, 2004
Messages:
5
Oops, sorry--I forgot the exponent part from cell K2 is actually this:

=10-COUNTBLANK(A2:J2)

6. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,891
First Name:
Wayne
I think you will need a macro
I treied the same approach and can change the starting equation based on number of blanks
using choose to get the starting cell and replace
a bit longwinded but executing the formula is a problem

=CHOOSE((10-COUNTBLANK(A1:J1)),"J","I","H","G","F","E","D","C","B","A")
then use replace but thats where it goes down the tubes, I can get the correct formula, but not to execute.

heres the full formula for calculating cells
a10 - j10

=REPLACE("((A10/B10)^(1/10-COUNTBLANK(A10:J10))-1)",3,1,(CHOOSE((10-COUNTBLANK(A10:J10)),"J","I","H","G","F","E","D","C","B","A")))

so a macro is probably the best way

7. ### XL Guru

Joined:
Aug 30, 2003
Messages:
2,702
No idea what CAGR is. I know where to steal stuff tho'. Try this ;

Select L2, paste this in the formula bar

=INDEX(A2:I2,MATCH(TRUE,LEN(A2:I2)>0,0))

and hit CTRL+Shift+Enter (array formula ; do it right & it'll show as

{=INDEX(A2:I2,MATCH(TRUE,LEN(A2:I2)>0,0))}

in the formula bar).

=J2/L2^(1/K2) in M2.

HTW,
Andy

Joined:
Oct 13, 2004
Messages:
5
Hi ~ CAGR is Compound Annual Growth Rate, used to determine financial growth. It is calculated based on the beginning / ending values and the number of periods involved. The formula for L2 pastes as an array fine, but overall this doesn't seem to work (yet). I'm still working on it...

9. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,891
First Name:
Wayne
cool XL_guru
however, K2 needs to count down
so if all the cells have numbers - it should equal 10

CAGR is compound annual growth rate
the formula in words
is the last year value / start year value raised to the power of one over the number of years minus 1

so if cell a2 or B2 is blank then the start year needs to be
last year value (j2 / C2 (the first none blank number) raised to the power of 1 divided by the count of the cells with numbers in so 8 in this case.

I just tried the formula and seem to be dividing numbers
OR
so the value in Cell J2 divided by the first non blank cell starting from A2 raised to the power of 1 divided by the count of cells a2 to j2 that do not have blanks in minus 1

10. ### XL Guru

Joined:
Aug 30, 2003
Messages:
2,702
>> however, K2 needs to count down

So that's COUNTA then (?)

11. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,891
First Name:
Wayne
just had another look its probably simple
to replace
=J2/L2^(1/(10-K2)-1)

edit
or
=J2/L2^((1/(COUNTA(A2:J2))-1))

edit
mmmmmmmmmmm---- that did not work cagr, on my test data confused ----mmmmmmmmmm

like the index part though - learnt something useful tonight thanks

12. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,891
First Name:
Wayne
aghhhhhh

just brackets I think
CAGR = (FV/PV)1/n - 1
http://www.moneychimp.com/glossary/cagr.htm

so i dont think you want to use the first year value
so i tried this
=(J14/L14)^((1/(COUNTA(A14:J14)-1)))-1

it works correctly
i put
5 in cell a2
then in cell B2 i put A2*1.05 then copied across the cells- so i'm increasing each year by 5% and so the CAGR should calculate at 5%

the number of years does not include the first so 9 years for a full set of data

but its late here in UK now and i've been on PC at work since 8am
night night

Joined:
Oct 13, 2004
Messages:
5
Hi ~

Ok, looks like I finally figured out how to do this. Since the exponent is really the driver here, I just key off of the K2 cell:

=IF(K2=9,((J2/B2)^(1/K2)-1),IF(K2=8,((J2/C2)^(1/K2)-1),IF(K2=7,((J2/D2)^(1/K2)-1),IF(K2=6,((J2/E2)^(1/K2)-1),IF(K2=5,((J2/F2)^(1/K2)-1),IF(K2=4,((J2/G2)^(1/K2)-1),IF(K2=3,((J2/H2)^(1/K2)-1),IF(K2=2,((J2/I2)^(1/K2)-1),((J2/A2)^(1/K2)-1)))))))))

It's not as elegant as some of the ideas posted on the thread, but it seems to give me what I needed, so all I need to do is monitor the data rows to make sure there are no gaps between the begining / ending periods going forward.

Thanks to all for the suggestions that were made!

Regards -

CP

As Seen On