Hey guys! I need help with nested VLOOKUP function to calculate estimated annual insurance premium based on the owner's region of residence,driving record, and residential status in cell F12.(Hint: Include an intermediate calculation in the form that looks up the region number associated with the state of residence, and then refer to the cell that contains this result in the insurance formula.)
Please help!!
Provide one or two COMPLETE examples of what the answers should be and explain your answers if they aren't obvious.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
well it should look something like this with the vlookups nested in IF(ISBLANK(Cell),”",A-B+C+D-E) where A is the Base Cost, B is the Deduction for Excellent Driver, C is the Addition for Poor Driver, D is the Addition for City Residence, and E is the deduction for Rural Residence.
It just gets really long and complicated, and confuses me to the point where i don't know where i went wrong, if it's my defining cell names the wrong way or what...
It's not a popular answer around here, but whenever I have a formula that "gets really long and complicated" to the point that it "confuses me," I break the formula up into manageable sections. Can I assume you are able to put together a single VLOOKUP() for each term in the summation? If so, I would perform each VLOOKUP() separately. With the result of each VLOOKUP() in its own cell, the final result would appear to be a simple =sum() function.It just gets really long and complicated, and confuses me to the point where i don't know where i went wrong,
Originally Posted by shg
ok i have this so far
=IF(ISBLANK(A12),"",VLOOKUP(region_,Insurance!$A$3:$B$7,2,FALSE)+IF($H$8="Excellent",-VLOOKUP(region_,Insurance!$A$3:$D$7,3,FALSE),IF($H$8="Poor",VLOOKUP(region_,Insurance!$A$3:$D$7,4,FALSE),0))+IF($H$7="Rural",-VLOOKUP(region_,Insurance!$A$3:$D$7,5,FALSE),IF($H$7="City",VLOOKUP(region_,Insurance!$A$3:$D$7,6,FALSE),0)))
it works fine and updates id the driving safety record changes but it doesn't update when the residential status changes....
Can you guys see whats wrong?
nevermind, i figured it out... thanks anyways
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks