=IF help

**Juvenyle** · 04-06-2010, 01:53 PM

(Name Sheet2 FML_IF-11): Cell B1 has a gender indicator of F or M. Cell B2 has an accident counter from 0 to 9. Cell B3 contains the standard monthly car insurance rate. Cell B4 is to have a discount percentage to apply to the standard rate, calculated as follows: If the driver is female, a 5% discount is given, if she has 0 accidents, then a 10% discount is given. If the driver is male with zero accidents, then a 5% discount is given. Provide the excel formula to display the correct discount rate in cell B4

any clue on how to start this?

**Paul** · 04-06-2010, 03:39 PM

Hi Juvenyle, welcome to the forum.

Perhaps try:

=B3*IF(AND(B1="F",B2=0),0.9,IF(OR(AND(B1="M",B2=0),B1="F"),0.95,1))

Or

=IF(OR(AND(B1="M",B2=0),AND(B1="F",B2<>0)),B3*0.95,IF(AND(B1="F",B2=0),B3*0.9,B3))

Both should give the same result, they're just formatted a little differently.

**Juvenyle** · 04-06-2010, 04:08 PM

I understand that part but I'm having to create the whole thing from scratch and don't know how to insert M or F in B1 0-9 in B2 and the car insurance rate in B3.

Sorry that I wasn't clear the teacher kind of confused me.

**Paul** · 04-06-2010, 04:10 PM

You simply type the letter M or F in B1. Then type a number from 0-9 in B2. Then type a car insurance rate in B3.

Am I missing something? Also, seeing as this is likely a homework assignment, future responses may be more of a push in the right direction rather than a full solution - you're supposed to be learning something here, not have it done for you.