Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

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

Posted on 2014-03-31
Medium Priority
215 Views
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

0
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2

LVL 35

Expert Comment

ID: 39968358
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))))
``````
HTH,
Dan
0

Author Comment

ID: 39968364
Dan,
It works ok, but does not satisfy condition of genders where age is > 35
0

Author Closing Comment

ID: 39968381
This worked. thanks so much for your help.
0

LVL 35

Accepted Solution

Dan Craciun earned 2000 total points
ID: 39968379
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))))
``````
You need an AND to test for between...
0

LVL 35

Expert Comment

ID: 39968395
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month8 days, 16 hours left to enroll