Excel Beginer in over my head.....Multi conditions
Posted by Rob on July 27, 2001 4:20 PM
I have an insurance spreadsheet with a column of dollar amounts. I need to insert another column with those numbers minus their deductibles, but only if submitted, (yes/no criteria). If an accident -$1000, if a theft on a jobsite -$2500, if a theft at a company building -$500. I am pulling my hair out!
Posted by Aladin Akyurek on July 27, 2001 4:27 PM
Rob,
Care to post about 6 rows of you data and a bit more specific about the criteria?
Aladin
PS. Don't know much about insurance.
Posted by Rob on July 27, 2001 4:58 PM
Me either,
Amount minus deductible is the column I need the formula for.
Header Row
Description, Employee, Fault, Submitted, Amount, Minus Deductible, Type of claim
Row 2
We hit a truck, Lisa, Ours, Yes, $2511.17, $1511.17, Accident
Row 3
Honda Civic ran into our parked Truck, Jim, Theirs, No, $912.45, 912.45, Accident
Row 4
Truck was stolen and vandalized, N/A, Theirs, Yes, 2289.00, 1789.00, Theft-Office
Row 5
Truck was stolen, N/A, Theirs, Yes, $17000, $14500, Theft-Site
Row 6
We hit a car on I-5, David, Ours, No, $300, $300, Accident
Row 7
Car hit us on a left turn, Sarah, Theirs, Yes, $2497, $1497, Accident
Thanks,
~Rob
Posted by Aladin Akyurek on July 27, 2001 5:53 PM
Rob,
Is "Type of Claim" is always filled in?
& you want a formula for the column "Minus deductible", right?
Assuming that the answers to both questions are yes and these labels are in A1:G1.
In F2 enter: =IF(ISNUMBER(E2),E2-IF(D2="No",0,IF(G2="Accident",IF(C2="Ours",1000,0),VLOOKUP(G2,{"Theft-Office",500;"Theft-Site",2500},2,0))))
Copy down as far as needed.
Hope you don't have additional conditions.
Aladin
