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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Simba1

Board Regular
Joined
Dec 13, 2004
Messages
165
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.
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,901
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!
 

clkeppel

New Member
Joined
Jan 24, 2005
Messages
2
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.
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,901

ADVERTISEMENT

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
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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)
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Still playing with this, I think this one's better:

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

Forum statistics

Threads
1,147,508
Messages
5,741,572
Members
423,668
Latest member
Audorin

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
Top