Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post

    Excel 2010 - Complicated Formula - help needed urgently!

    Hi All,

    I've attached a small spreadsheet with which I need urgent help. I have a workbook with 2 sheets

    Rates Sheet

    The Rates Sheet contains a set of hourly rates for a series of different staff grades for various years. These tables are identified using named ranges. The currently selected table for use in calculations is called "RatesTableRange". The range containing the years within the RatesTableRange is called RatesYearRange and the range containing the applicable staff grades is called RatesEffortRange.

    Sheet 1

    On Sheet 1, i have a table where you can enter for each month the number of hours at the selected staff grade, but for each month (column) the applicable rate may be different depending on the year. The year for each column is shown on sheet 1, row 2. The applicable staff grade for each row is in column E.

    I need help to get the right formula for row 3, which calculates the total costs for that column, by calculating effort (in that column) * rate (looked up in the RatesTableRange). To get the right rate, the formula must look up in the RatesTableRange the correct rate depending on the grade (sheet 1, column E) and the year (sheet 1, row 2).

    I've tried all sorts of array formulae using SUMPRODUCTS, VLOOKUPs, MATCHES etc and I just can't get it right!

    Can you help to define the right formula for me!?

    many thanks folks!
    Dominic
    Attached Files Attached Files

  2. #2
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post
    as if by some miracle, I stumbled across the following formula, which doesn't used named ranges (yet) but substituting fixed ranges for named ranges should be quite straight forward!

    =IF(M$3<>"",SUMPRODUCT(WBSEffortRange*IF(ISNA(LOOK UP(WBSGradeRange,Rates!$B$21:$B$28,INDEX(Rates!$C$ 21:$M$28,,MATCH(M$3,Rates!$C$20:$M$20,0)))),0,LOOK UP(WBSGradeRange,Rates!$B$21:$B$28,INDEX(Rates!$C$ 21:$M$28,,MATCH(M3,Rates!$C20:$M$20,0))))),"")

  3. #3
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post
    Quote Originally Posted by dom_donald View Post
    as if by some miracle, I stumbled across the following formula, which doesn't used named ranges (yet) but substituting fixed ranges for named ranges should be quite straight forward!

    =IF(M$3<>"",SUMPRODUCT(WBSEffortRange*IF(ISNA(LOOK UP(WBSGradeRange,Rates!$B$21:$B$28,INDEX(Rates!$C$ 21:$M$28,,MATCH(M$3,Rates!$C$20:$M$20,0)))),0,LOOK UP(WBSGradeRange,Rates!$B$21:$B$28,INDEX(Rates!$C$ 21:$M$28,,MATCH(M3,Rates!$C20:$M$20,0))))),"")
    Hmm this doesn't work either... I need help

  4. #4
    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
    Dom,

    Attached is a possible solution to your problem. I think your basic problem is trying to do too much in one place. Thus, I made a copy of your Sheet1 table below it and use that to calculate the money amounts with simple VLookups {note I created my own table Name ,MyRateTable, since you need to define the whole table range (less column headers) for the VLookup to work. I know this isn't a final solution but should get you far enough along to finish it off.

    FYI: you could place the secondary table on another sheet if you want it out of site just adjust the formulas as appropriate. Also notice the use of the $ to make the formulas copyable, i.e. you enter it once and then just drag it around.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post
    hi there,
    thanks for your suggestions! Yeah, I know I was trying to do a lot in one place, but it was to try to avoid having hidden sheets - and besides I was just trying to be clever But yes, I think it will be necessary to have a hidden sheet. My actual spreadsheet allows the user to specify the start and end dates of the "project" for which the information is being entered, so the size of the data entry area is dynamic. The new hidden sheet with the costs will just have to dynamically change size in the same way as the data entry sheet. Shouldn't be too much of a problem!
    I'll go down that path, using your examples which I can understand fine and we'll see where I get to.

    Meanwhile, if any one can get a one-formula solution, I'd be impressed

    cheers
    Dom

  6. #6
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post
    OK, thanks.. I've sorted it now using the hidden sheet, which is dynamically modified in exactly the same way as the data entry sheet so when the user selects different start and end dates for the data entry, the other sheet changes correspondingly. The rest is then simple, so thanks for pushing me in that direction!

    I'm not aware of any more bugs in this sheet now, but I'm sure I'll find one tomorrow

  7. #7
    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
    Dom,

    Glad you got it sorted out! I've always found that the KISS rule serves me well. Complicated formulas while cool are a pain in the, you know where, when you have to go back and make changes 6 months later. Post back if you need more assistance.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  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
    I think this will work - in K3:
    =SUMPRODUCT(MMULT(--(TRANSPOSE(RatesNameRange)=Sheet1!$E$5:$E$11),INDE X(Rates!$C$21:$M$28,0,MATCH(K$2,Rates!$C$20:$M$20, 0))),K5:K11)
    array entered (with Ctrl+Shift+Enter) and then fill across.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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