1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

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

Thread Status:
Not open for further replies.
Advertisement
  1. cpayne1757

    cpayne1757 Thread Starter

    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

    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. etaf

    etaf Moderator

    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
    so instead of =((J1/A1)^(1/10)-1).
    are you looking for =((J1/D1)^(1/7)-1).
     
  4. cpayne1757

    cpayne1757 Thread Starter

    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
     
  5. cpayne1757

    cpayne1757 Thread Starter

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

    =10-COUNTBLANK(A2:J2)
     
  6. etaf

    etaf Moderator

    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

    XL Guru

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

    Your =COUNTBLANK(A2:J2) in K2.

    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
     
  8. cpayne1757

    cpayne1757 Thread Starter

    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. etaf

    etaf Moderator

    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

    XL Guru

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

    So that's COUNTA then (?)
     
  11. etaf

    etaf Moderator

    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. etaf

    etaf Moderator

    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 :)
     
  13. cpayne1757

    cpayne1757 Thread Starter

    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
     
  14. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/284094

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice