Premium Calculations

[sagar]

Dear Team,

Please help me in fixing the premium calculation as per the attached file. For more details kindly refer the attached file.

Regards
Sagar

[alansidman]

See Attached. I do not understand what you are looking for in cell F13. Look at the formulas in G4, G9,G10 and G11.

[Ace_XL]

With alansidman's solution use this in M4

Please Login or Register  to view this content.

[newdoverman]

I have re-worked your sheet. It might be what you are looking for.

[sagar]

Hi alansidman,

Thanks for your calculations, sorry for the confusion,

I need formulae in (Calculations sheet) “M1” cell, to calculate the premium, based the value in “I4” cell, IF “I4” is “1” our formulae has to take values from database sheet “B column” values based on the age in Calculations sheet “G4” cell.

IF “I4” is “2” our formulae has to take values from database sheet “C column” values based on the age in Calculations sheet “G4” cell.

IF “I4” is “3” our formulae has to take values from database sheet “D column” values based on the age in Calculations sheet “G4” cell.

Hope it is clear

Thanks and Regards
Sagar

[sagar]

I have re-worked your sheet. It might be what you are looking for.

Hi, Thank you very much for your help, I am not able to understand your formulae,

=VLOOKUP(G4,Tax,I4+1,1)*L4/365

What is Tax+1,1, doing it here

please help me to understand.

Thanks - Sagar

[abubaniyan]

as your area is confined of 4 columns. to show the formua from which coloumn the data should be fetched form they have added 1. as the first column represents the "age" and from other your actual data starts.

[newdoverman]

Sorry for the delay. Tax in the vlookup is a named range on the Database sheet. The I4+1 indicates which column in the named range to look in.

Database!$A$75:$D$145

Using a name for a range allows you to refer to that range without having to enter the cell addresses of the range which eliminates typo mistakes.

The range called Tax is an expanded table showing all possibilities of age and category. The vlookup formula gets the data as indicated by the contents of G4 and I4. The +1 is there to calculate which column from the range is to be referenced.

Hope this helps