# Thread: Excel 2010 - Complicated Formula - help needed urgently!

1. ## 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

2. 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!

3. Originally Posted by dom_donald
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!

Hmm this doesn't work either... I need help

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

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

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

#### Posting Permissions

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