Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post
    I think I am having a brain fade at the moment and need to solve this problem quickly

    I have a long formula in a cell on a spreadsheet which, for each row, multiplies a value by a rate found in another table. The rate is looked up based on a category in the original range :

    =IF(H3 <> "",SUMPRODUCT(H6:H101*LOOKUP($E$6:$E$101,Rates!$B$ 4:$B$7,INDEX(Rates!$C$4:$M$7,,MATCH(H3,Rates!$C$3: $M$3,0)))),"")

    The problem is that the sumproduct ranges are not always fixed i.e. H6:H101 and E6:E101 are not always fixed - it depends how many lines of data the user fills in as input data. It could be H6:500 and E6:E500. My formula doesn't handle the fact that the range is variable. If the user only enters 101 rows of data, the 102nd row will not contain a category in column E, leading to a failed lookup on the rates, resulting in a #N/A sumproduct overall. I would like the formula just to ignore (or add zero to the sumproduct) if the column E value is empty.

    By my reckoning, I could either:

    1) Update the formula so it always does a sumproduct on the maximum range H6:H500 and E6:E500, however the lookup currently only works if the category in column E is not blank. If the value in column E is blank, the formula returns a #N/A. So if the user only inputs 101 rows, I need to change the formula so it ignores from 102 to 500 where column E is blank.

    2) Dynamically build the sumproduct range in the formula using VB or some other method. I have already stored the row number of the last row that the user input in a cell, i.e. if the user input 101 rows, the cell contains the value 101, or if he input 500 rows, it contains the value 500. Perhaps there is some way I can modify the formula to avoid hardcoding the ranges.

    Bit difficult to explain.

    Cheers,
    Dom
    Attached Images Attached Images

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Donald,

    What you need is dynamic named ranges:

    =OFFSET(Sheet1!$E$6,0,0,COUNTA(Sheet1!$E$6:$E$1000 0),COUNTA(Sheet1!$E6$2:$E$10000))

    Use this formula inside the Define Name box as the Refers to: value and give it a name. As long as there are less than 10,000 rows it will adjust automatically. Increase/Decrease the number {in both places} if your likely maximum number of rows is more or less. Create a named range like this for each of your ranges. Then just use the names in your worksheet formulas.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post
    thanks for the reply..

    I have created my two named ranges for the grade and the number of hours in columns E and H respectively but I needed to amend your suggestion.
    The reason is that the "dynamic range" definitions should only be 1 column wide in each case. I amended the range definitions to:

    WBSEffortRange =OFFSET(WBS!$E$6,0,0,COUNTA(WBS!$E$6:$E$1000),1)
    WBSGradeRange =OFFSET(WBS!$H$6,0,0,COUNTA(WBS!$H$6:$H$1000),1)

    This works fine...

    HOWEVER

    Is there another way to solve the same problem? I have been asked by users to allow blank rows and for them just to be discounted. Is there a way to do the sumproduct/lookup/index/match differently so it doesn't mind if I don't have a grade in column E - so for the blank row the product is just zero? This would both enable blank lines to exist and would also allow me to do away with the "dynamic ranges" and just have a static maximum range for the sumproduct?

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Is the WPID column always populated for each row?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post
    nope, a blank row would be a completely blank row..

    I was hoping you might be able to put some kind of IF function around the lookup to say "return 0 if you can't find the rate in the table, rather than a #N/A".

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I'd define your ranges as:
    WBS!$E$6:INDEX(WBS!$E:$E,LOOKUP("ZZZ",WBS!$B:$B,ROW(WBS!$B:$B)))


    otherwise they will be incorrect using COUNTA if you have blank rows.


    For the SUMPRODUCT, you'll have to include an ISNA() or ISNUMBER() check.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post
    Hi.. could you give me a complete example? I'm not so hot on these monster formulae

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Basically:

    =IF(H3 <> "",SUMPRODUCT(H6:H101*IF(ISNA(lookup_formula),0,lo okup_formula)),"")


    It may be possible to simplify things, but we'd need to see a workbook.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post
    Hi Rory,
    thanks for the info.

    I modified my formula to be

    =SUMPRODUCT(WBSEffortRange*IF(ISNUMBER(LOOKUP(WBSG radeRange,Rates!$B$4:$B$7,INDEX(Rates!$C$4:$M$7,,M ATCH(H3,Rates!$C$3:$M$3,0)))),LOOKUP(WBSGradeRange ,Rates!$B$4:$B$7,INDEX(Rates!$C$4:$M$7,,MATCH(H3,R ates!$C$3:$M$3,0))),0))

    which is essentially

    =SUMPRODUCT(efforts*rates) where the rate is looked up in a table on the "Rates" sheet depending on the year (row 5) and the grade (column 5). If the Grade (column 5) is blank, I want that product/row to return 0 i.e. contribute nothing to the total SUMPRODUCT.

    The formula I created now seems to return 0 for the whole SUMPRODUCT - i.e. the whole calculation results in 0.

    I attach my workbook. I would like row 5 , cols H - BC to show the total cost of the column. Blank rows should be permissible (at the moment I have to have a grade in col E), so the named range definitions "WBSEffortRange" and "WBSGradeRange" will have to change.

    Hope you can help!

    cheers
    Dom
    Attached Files Attached Files

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You need to array-enter that (Ctrl+Shift+Enter) for it to work.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post
    ok, done that.. makes no (noticeable) difference. The total for the column is still zero where it shouldn't be..

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I'd have to guess you did something wrong then, as it works fine in your sample file. Does the formula appear in the formula bar as:
    {=SUMPRODUCT(WBSEffortRange*IF(ISNUMBER(LOOKUP(WBSG radeRange,Rates!$B$4:$B$7,INDEX(Rates!$C$4:$M$7,,M ATCH(H3,Rates!$C$3:$M$3,0)))),LOOKUP(WBSGradeRange ,Rates!$B$4:$B$7,INDEX(Rates!$C$4:$M$7,,MATCH(H3,R ates!$C$3:$M$3,0))),0))}

    Note: you have to array-enter it into one cell, and then fill it across using the fill handle. See attached file.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post
    Hi Rory,

    thanks again.. I realised what was wrong last night when I was drifting off to sleep!

    The formula is


    =IF(K$3<>"",SUMPRODUCT(WBSEffortRange*IF(ISNA(LOOK UP(WBSGradeRange,Rates!$B$4:$B$7,INDEX(Rates!$C$4: $M$7,,MATCH(K3,Rates!$C$3:$M$3,0)))),0,LOOKUP(WBSG radeRange,Rates!$B$4:$B$7,INDEX(Rates!$C$4:$M$7,,M ATCH(K3,Rates!$C$3:$M$3,0))))),"")


    There was also a mistake in my range definitions which also caused #N/A even with the formula above. I used the rather poor formula auditing functionality to suss this one out.

    I just have to modify my range definitions to use the INDEX instead of the COUNTA to accommodate blank lines. In your formula you suggested

    WBS!$E$6:INDEX(WBS!$E:$E,LOOKUP("ZZZ",WBS!$B:$B,RO W(WBS!$B:$B)))

    Can you let me know what the "ZZZ" part is? I'm not used to using INDEX / LOOKUP formulae...

    cheers! (then I'll let you be, I promise )

    Dom

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    The ZZZ is just a character string that will be 'larger' (in alphabetical order terms) than any value in that column - it's just to make sure the LOOKUP returns the last value.
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #15
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post
    okie doke.. thanks.

    I have a user defined function called "GetWBSEndRowNum()" which works fine and returns the last row entered by the user. Can I use this in the range definitions instead of this lookup/index method? I have tried a few things and i haven'T figured it out yet.

    cheers
    Dominic

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •