• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • Last Modified:

Excel: how to use if- and statements to calculate fee

I need to use logic statements to determine fee for a service depending on age and gender

I have the following source table:

                                  M        F
Less than 18 years             100      80
18 to 35                              200      170
More than 35 years             300      270      

where gender will be in column B, age in column C, and fee amount in column D
so i basically need a function that'll read something like:

if  (B1 = "F") then
    if (C1  < 18) then 80
    else if (C1>=18 or C1 <=35) then 170
    else 270
else if (B1 = "M") then
    if (C1  < 18) then 100
    else if (C1>=18 or C1 <=35) then 200
    else 300

please assist?
  • 3
  • 2
1 Solution
Dan CraciunIT ConsultantCommented:
Try this:
=IF(B1="F", (IF(C1<18, 80, IF(OR(C1>=18, C1<=35), 170, 270))), IF(B1="M", IF(C1<18, 100, IF(OR(C1>=18, C1<=35), 200, 300))))

Open in new window

badtz7229Author Commented:
It works ok, but does not satisfy condition of genders where age is > 35
badtz7229Author Commented:
This worked. thanks so much for your help.
Dan CraciunIT ConsultantCommented:
That's because I replicated your algorithm that has a flaw :)
=IF(B1="F", (IF(C1<18, 80, IF(AND(C1>=18, C1<=35), 170, 270))), IF(B1="M", IF(C1<18, 100, IF(AND(C1>=18, C1<=35), 200, 300))))

Open in new window

You need an AND to test for between...
Dan CraciunIT ConsultantCommented:
Glad I could help!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now