# Thread: IF Statement and LookUp Together In Excel (Excel 2000)

1. ## IF Statement and LookUp Together In Excel (Excel 2000)

Hi,

I'm trying to do quite a complicated budget calculation in Excel that uses a couple of look ups and then gives me a budget variance, would it be possible to help with this please?
It's an NHS query and I have a budget by Specialty for each Hospital. The data is listed by individual patient, so we need to do actual cost - budget cost somehow. So far i've tried to do the sum of tariff (but to do this I need to match the trust and specialty in a lookup) and this then finds the tariff on those lines and for that specialty and adds them up. Then I need to subtract from this the average of the budget (so again this has to look up specialty and trust). Is there a quick way of doing this using an IF Statement?
I've attached the file so you can see my attempts so far in the last couple of columns.

2. ## Re: IF Statement and LookUp Together In Excel (Excel 2000)

Your worksheet contains an ad-hoc budget value for breast surgery in AC5. There are no corresponding numbers for cardiology, dermatology etc., so it escapes me how we could provide formulas. You'd need to have some kind of regular data table, not an ad hoc value here and there.

3. ## Re: IF Statement and LookUp Together In Excel (Excel 2000)

Hi Hans,

Thanks very much for your reply. We'd actually like to try and do it without using the values in AB and AC, that was our original workings. I've attached a slightly revised version now which shows a calculation that we have done for the budget - using our look up. We need to calculate a variance though and then show this in the pivot on sheet 3 if possible, in column E. So the variance in the pivot would be Sum Of Tariff - Average Of Budget, but we are not sure of how to get the variance column into the datasheet.
Thanks for any help!

4. ## Re: IF Statement and LookUp Together In Excel (Excel 2000)

Your pivot table hasn't been updated, so it contains incorrect values.
Do you really want to subtract the average of budget from the sum of tariff? For breast surgery, that would result in 1134 - 167 = 967. I'm not sure that that is what you intended.

5. ## Re: IF Statement and LookUp Together In Excel (Excel 2000)

Hi Hans,

Yes that's what we're trying to achieve in the pivot table. Either by having a variance column in our source data or directly in the pivot table using the formula function and 'calculated field'.
We'd be greatful for any help.

6. ## Re: IF Statement and LookUp Together In Excel (Excel 2000)

Does the attached version do what you want?

7. ## Re: IF Statement and LookUp Together In Excel (Excel 2000)

Hi Hans,

That's absolutely perfect! We've been tearing our hair out for ages with this and this works exactly how we want it.

Thanks again and sorry to trouble you with it.
Regards
Andy

8. ## Re: IF Statement and LookUp Together In Excel (Excel 2000)

Hi Hans,

Sorry to trouble you again. We are still working on our budget data and are nearly there with it now. We just have a few problems with our pivot table.

Do you know how we can show an accurate grand total at the bottom in the pivot for the average of the budget? Secondly, we are having problems in our pivot table with our drop down selections. They work fine on the individual page drop down selections, if we select a trust or type of attendance (new or follow up), however if we slect 'all' the calculation doesn't work - is there any way around this?

Thanks again
Andy

9. ## Re: IF Statement and LookUp Together In Excel (Excel 2000)

I removed the column totals from the pivot table because they are meaningless for the Variance column. You could add them again, keeping that in mind. You'll get a correct total count of attendance dates, sum of tariff and average of budget, but the result for variance is is nonsense, because it is a combination of sum and average.

I see no problem with the dropdowns. Could you provide an example of where the result is wrong in your eyes?

10. ## Re: IF Statement and LookUp Together In Excel (Excel 2000)

Attached is our latest working file, using your formulae from earlier. This works fine if you put 'patient type' in the page drop down to either new or follow up. However if you select 'all' then the calculation in the pivot doesn't quite work eg for ENT. Similarly if you select individual trusts it seems to work, but if you select 'all' on this it fails for certain specialties - it looks as though it's the ones that have got both new and follow up attends together.

Thanks.
Andy

11. ## Re: IF Statement and LookUp Together In Excel (Excel 2000)

This problem didn't occur in the file you attached earlier, since all rows had "First attendance". Now that you have attached a file that has both "First attendance" and "Follow up attendance", I see the discrepancy.
I don't see a solution, except adding formulas to the right of (and hence outside) the pivot table. But basically, I don't understand the calculations - as far as I can tell they are meaningless. Perhaps someone else has an idea.

12. ## Re: IF Statement and LookUp Together In Excel (Excel 2000)

Andy, can you define "Doesn't work" - why, what results do you expect? If you apply an autofilter on the source data and run =SUBTOTAL(type, range), i get the same results as your pivot table. For that matter I also re-engineered the pivot table as a group of array formulas, and they also get the same result. See attached..

Averaging columns J and K, Budget and Variance, doesn't seem meaningful, since columns J and K contain the total budget and total variance for the entire specialty listed repetitively for each case, different only by Initial or Follow-up. If you want correct per specialty averages, column J will need to be the per case budget, and column K will need to be the per case variance. Or, I have no idea what your goal is. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

#### Posting Permissions

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