Multiple IF Statements

[day92]

I have a project im working on and having a little trouble getting started. Im trying to analyze a large amount of data. I have a data sheet with about 10k records and im trying to compare it to our 2010 rates.

The formula needed is to compare our membership to the new rate by ***, age, plan, co pay * a risk factor. I attached a spreadsheet of what im talking about.

For example: the first row in the data tab is a 5yr old male in our pers plan with a co pay of $15, therefore I would use $38.16*.975 to get my final number. I guess I could use multiple IF statements but does anyone have any other ideas to do this for 10k members?

[MarvinP]

Hi day92

Find the attached. I had to fix your age ranges so VLookup would work. I didn't understand the two 65 year old categories. I also created three named ranges, one for Male and the other for Female and a third for the CoPayFactor lookup table. See if the attached works for you.

[day92]

Yes the formula works but what is the point of the Match and the Vlookup on 2010 tab?

[day92]

Actually it does not work when I drag it down. It does not pick of the POS members. Take a look at the attached sheet.

[MarvinP]

Hi,
On the 2010 Tab you have an extra space after "POS ". If you remove the space after POS the formula will work.

The Match and VLookups from the 2010 tab use 3 named ranges to get the values needed to do the calculations. Does that make sense? I hope you can use the formula or move it to where you need it.

[day92]

Yes it works, thanks for your help.