Excel Formula Help in Numbers

Discussion in 'Productivity Apps Forum' started by sgt_hoser, Oct 17, 2012.

  1. sgt_hoser
    Offline

    sgt_hoser iPF Noob

    Joined:
    Oct 17, 2012
    Messages:
    1
    Thanks Received:
    0
    Trophy Points:
    0
    Ratings:
    +0 / 0
    I have a formula that works great in Excel, and that I have not been able to re-write it to work in Numbers. Here it is.

    =IF(LEN(B2)>0,SUM(A3:INDIRECT("A"&ROW()+IFERROR(MATCH("*$",A3:A52,0),50))),"")

    I realize that everone one of these individual formulas are supported in Numbers, it seems to be the nested INDIRECT portion causing the grief. When I try to open this in Numbers I get a message saying the formula is not supported, and it has pasted the last values.

    Is there a way to write this formua to to work in both Excel and Numbers?

    If you are curious, this formula is writen to SUM a variable range of values between BarCodes (containing the $). The value string that needs to be summed can be as short as 1 value between each bar code, and up to 15-20 rows or values between.

    Any Ideas?

    Hoser
  2. twerppoet
    Offline

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,407
    Thanks Received:
    2,033
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,317 / 1
    Off the top of my head I see one problem. The SUM function can only take one variable (sort of); either a range or column/row. To make the INDIRECT function work inside it you'll have to generate the pointer to the entire address, not just half of it. I also suspect that the stuff going on inside the INDIRECT statement will have to be done differently.

    As for the rest, I really don't understand what you are doing well enough to guess at a workable formula. If you can supply a sample sheet I might be able to come up with something.

Share This Page

Search tags for this page

how to show excel formulas in numbers ipad

,

numbers formula help