I need help with "if, then" statements

clkeppel

New Member
Joined
Jan 24, 2005
Messages
2
What I am trying to do is see the result of a formula if two conditions hold true. For example I currently have a formula set up that will calculate our liability at different cost levels. Now anything prior to FY 2005 is calcualted one way and and any costs incurred in FY 2005 are subject to a different cost limitation. So what I would like to be able to do is write a statement that says if this and this holds true this is the result and the different levels. Hopefully I have made myself clear and someone can help me out. Thanks!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
You'll have to nest an "And" statement in your "If" statement. I can only be vague right now because your original post is not too detailed. You can find help on this in the "help" section of Excel as well.
 
Upvote 0
If you place this code in C1 you require 1 in both A1 and B1 in order to have "TRUE" otherwise C1 remains "FALSE". Subtitute your calculations for "TRUE" and "FALSE" in the formula. Hope this helps. Dave
Code:
=IF(B1=1,(IF(A1=1,TRUE,FALSE)),FALSE)
edit: Welcome to the Board!
 
Upvote 0
ok, hopefully this will clarify a bit. Prior to FY 2005 any amount under $25K we are responsible for 100%, between 25K and 100K we split 50/50 and anything over $100K we are not responsible for. In 2005 we are not responsible for any amount over $25K. These costs are based on individual hospital stays so we are still getting costs coming in for prior years. I need a formula that will calculate our costs based on the year in which the hospital stay occured.
 
Upvote 0
That is a little bit more challenging than my initial simplistic conception. I came up with this. It's ugly but seems to work as I understand your needs. Good luck. Dave
Code:
=IF(A1<2005,IF((B1>0)*AND(B1<=25000),B1,IF((B1>25000)*AND(B1<=100000),25000+0.5*(B1-25000),IF(B1>100000,62500,0))),IF(B1>=25000,25000,IF(B1<0,0,B1)))
edit: A1 is year. B1 is amount
 
Upvote 0
Assuming A1 has a year, and B1 an amount, perhaps:

=(CHOOSE(MATCH(B1,{0,25000,100000},1),1,0.5,0)*B1)*(A1<2005)

If A1 has an actual date:

=(CHOOSE(MATCH(B1,{0,25000,100000},1),1,0.5,0)*B1)*(YEAR(A1)<2005)
 
Upvote 0
Still playing with this, I think this one's better:

=(VLOOKUP(B1,{0,1;25000,0.5;100000,0},2,1)*B1)*(A1<2005)
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top