Spreadsheet to calculate premium

Question

I have a spreadsheet.&nbsp; On page one is the age bands for example 0-10, 11-20.&nbsp; 1-10 has a rate of .25 and 11-20 a rate of .26.&nbsp;Page 1Age&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Rate0-10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .251-10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .26&nbsp;On page 2 of my spreadsheet I have a column with age, a column with the benefit volume and the premium column.&nbsp; My premium column needs to be the result of the benefit volume * the rate on page 2 / 10.&nbsp; It would also need to be a maximum of 2000.&nbsp;Page 2Age Column&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Benefit Column&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; St. Premium5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 713&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Result&nbsp;I would normally try and do nested If functions but was told a Vlookup or Xlookup would be the way to do this?&nbsp; I have tried but just dont seem to be able to get it to work

rajesh_sinha · Answer

Kdahle&nbsp;For better understanding, pleases share the WB with us&nbsp;

Forum Discussion

Spreadsheet to calculate premium

1 Reply

Resources