|
Excell formula help
I have this formula in a cell (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95+BJ94).
this formula does not function correctly in all conditions. In certain conditions it needs to run like this (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95). and in another condition it need to be this (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-(bf109-bf113) What I need to do is set a condition something like this =if(Bf113<bf109) choose a say this formula (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-(bf109-bf113) So the final question I have is! Is it possible to set say three conditions and have a formula and it choose a separate formula per each condition. in a single cell. |
Excell formula help
Try:
=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-(BF109-BF113)*(BF113<BF109) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Brian" wrote in message ... I have this formula in a cell (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95+BJ94). this formula does not function correctly in all conditions. In certain conditions it needs to run like this (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95). and in another condition it need to be this (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-(bf109-bf113) What I need to do is set a condition something like this =if(Bf113<bf109) choose a say this formula (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-(bf109-bf113) So the final question I have is! Is it possible to set say three conditions and have a formula and it choose a separate formula per each condition. in a single cell. |
Excell formula help
That will of course subtract from either BJ95 or BJ95+BJ96 when ever
BF113<BF109 is true which may not be what you want. Actually now that I take the time to read your formula: SUM(BJ94:BJ96) must always be equal to or bigger than BJ95 because BJ95 is contained withing the SUM() so the test must always be False! Did you mean SUM(BJ94,BJ96) which is BJ94 + BJ96? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... Or with fewer key stroks but with one more function call: =IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-MAX(BF109-BF113,0) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... Try: =IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-(BF109-BF113)*(BF113<BF109) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Brian" wrote in message ... I have this formula in a cell (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95+BJ94) . this formula does not function correctly in all conditions. In certain conditions it needs to run like this (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95). and in another condition it need to be this (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-(bf109-bf113) What I need to do is set a condition something like this =if(Bf113<bf109) choose a say this formula (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-(bf109-bf113) So the final question I have is! Is it possible to set say three conditions and have a formula and it choose a separate formula per each condition. in a single cell. |
Excell formula help
No I'm wrong again! BJ94 or BJ96 could be negative.
So assuming that the test is correctly written to subtract from BJ96+BJ95 only use: =IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95-MAX(BF109-BF113,0)) To subtract from BJ95 only use: =IF(SUM(BJ94:BJ96)<BJ95-MAX(BF109-BF113,0),BJ95,BJ96+BJ95) To subtract from either use my previous formula. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... That will of course subtract from either BJ95 or BJ95+BJ96 when ever BF113<BF109 is true which may not be what you want. Actually now that I take the time to read your formula: SUM(BJ94:BJ96) must always be equal to or bigger than BJ95 because BJ95 is contained withing the SUM() so the test must always be False! Did you mean SUM(BJ94,BJ96) which is BJ94 + BJ96? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... Or with fewer key stroks but with one more function call: =IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-MAX(BF109-BF113,0) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... Try: =IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-(BF109-BF113)*(BF113<BF109) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Brian" wrote in message ... I have this formula in a cell (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95+BJ94 ). this formula does not function correctly in all conditions. In certain conditions it needs to run like this (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95). and in another condition it need to be this (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-(bf109-bf113) What I need to do is set a condition something like this =if(Bf113<bf109) choose a say this formula (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-(bf109-bf113) So the final question I have is! Is it possible to set say three conditions and have a formula and it choose a separate formula per each condition. in a single cell. |
Excell formula help
Sandy, Hi!
what I'm trying to do is set three conditions each with formula set to each condition. I'm thinking I don't need theis part of the existing formula (=IF(SUM(BJ94:BJ96)<BJ95,BJ95 and I can get rid of an if function The following are two of the three formulas that work independently. Formula if Condition BF109 BF113 exists! =IF(SUM(BA94:BA96)<BA95,BA95,BA96+BA95) Formula if condition BF109<BF113 exists! =IF(SUM(BA94:BA96)<BA95,BA95,BA96+BA95+BA94) I'm so confussed right now I can't remember the third condition? Thanks for your help! "Sandy Mann" wrote: That will of course subtract from either BJ95 or BJ95+BJ96 when ever BF113<BF109 is true which may not be what you want. Actually now that I take the time to read your formula: SUM(BJ94:BJ96) must always be equal to or bigger than BJ95 because BJ95 is contained withing the SUM() so the test must always be False! Did you mean SUM(BJ94,BJ96) which is BJ94 + BJ96? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... Or with fewer key stroks but with one more function call: =IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-MAX(BF109-BF113,0) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... Try: =IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-(BF109-BF113)*(BF113<BF109) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Brian" wrote in message ... I have this formula in a cell (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95+BJ94) . this formula does not function correctly in all conditions. In certain conditions it needs to run like this (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95). and in another condition it need to be this (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-(bf109-bf113) What I need to do is set a condition something like this =if(Bf113<bf109) choose a say this formula (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-(bf109-bf113) So the final question I have is! Is it possible to set say three conditions and have a formula and it choose a separate formula per each condition. in a single cell. |
Excell formula help
I'm so confussed right now I can't remember the third condition?
You're confused? <g But you still don't say when you want to subtract (BF109-BF113). -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Brian" wrote in message ... Sandy, Hi! what I'm trying to do is set three conditions each with formula set to each condition. I'm thinking I don't need theis part of the existing formula (=IF(SUM(BJ94:BJ96)<BJ95,BJ95 and I can get rid of an if function The following are two of the three formulas that work independently. Formula if Condition BF109 BF113 exists! =IF(SUM(BA94:BA96)<BA95,BA95,BA96+BA95) Formula if condition BF109<BF113 exists! =IF(SUM(BA94:BA96)<BA95,BA95,BA96+BA95+BA94) I'm so confussed right now I can't remember the third condition? Thanks for your help! "Sandy Mann" wrote: That will of course subtract from either BJ95 or BJ95+BJ96 when ever BF113<BF109 is true which may not be what you want. Actually now that I take the time to read your formula: SUM(BJ94:BJ96) must always be equal to or bigger than BJ95 because BJ95 is contained withing the SUM() so the test must always be False! Did you mean SUM(BJ94,BJ96) which is BJ94 + BJ96? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... Or with fewer key stroks but with one more function call: =IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-MAX(BF109-BF113,0) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... Try: =IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-(BF109-BF113)*(BF113<BF109) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Brian" wrote in message ... I have this formula in a cell (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95+BJ94) . this formula does not function correctly in all conditions. In certain conditions it needs to run like this (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95). and in another condition it need to be this (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-(bf109-bf113) What I need to do is set a condition something like this =if(Bf113<bf109) choose a say this formula (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-(bf109-bf113) So the final question I have is! Is it possible to set say three conditions and have a formula and it choose a separate formula per each condition. in a single cell. |
Excell formula help
mmmmm.....
Sandy Mann" wrote in message ... But you still don't say when you want to subtract (BF109-BF113). Not a ver informative question is it? What I meant was if (BF109<BF113) do you want to subtract (BF109-BF113) from BA95 or BA96+BA95+BA94? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... I'm so confussed right now I can't remember the third condition? You're confused? <g But you still don't say when you want to subtract (BF109-BF113). -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Brian" wrote in message ... Sandy, Hi! what I'm trying to do is set three conditions each with formula set to each condition. I'm thinking I don't need theis part of the existing formula (=IF(SUM(BJ94:BJ96)<BJ95,BJ95 and I can get rid of an if function The following are two of the three formulas that work independently. Formula if Condition BF109 BF113 exists! =IF(SUM(BA94:BA96)<BA95,BA95,BA96+BA95) Formula if condition BF109<BF113 exists! =IF(SUM(BA94:BA96)<BA95,BA95,BA96+BA95+BA94) I'm so confussed right now I can't remember the third condition? Thanks for your help! "Sandy Mann" wrote: That will of course subtract from either BJ95 or BJ95+BJ96 when ever BF113<BF109 is true which may not be what you want. Actually now that I take the time to read your formula: SUM(BJ94:BJ96) must always be equal to or bigger than BJ95 because BJ95 is contained withing the SUM() so the test must always be False! Did you mean SUM(BJ94,BJ96) which is BJ94 + BJ96? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... Or with fewer key stroks but with one more function call: =IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-MAX(BF109-BF113,0) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... Try: =IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-(BF109-BF113)*(BF113<BF109) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Brian" wrote in message ... I have this formula in a cell (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95+BJ94) . this formula does not function correctly in all conditions. In certain conditions it needs to run like this (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95). and in another condition it need to be this (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-(bf109-bf113) What I need to do is set a condition something like this =if(Bf113<bf109) choose a say this formula (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-(bf109-bf113) So the final question I have is! Is it possible to set say three conditions and have a formula and it choose a separate formula per each condition. in a single cell. |
Excell formula help
Brian,
Re-reading the posts is seems to me that you may want (BF109-BF113) subtracted from the minimum of BJ95 or SUM(BJ94:BJ96) but only if BF109BF113. If that is the case then: =MIN(SUM(BJ94:BJ96),BJ95)-MAX(BF109-BF113,0) Should do it. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... mmmmm..... Sandy Mann" wrote in message ... But you still don't say when you want to subtract (BF109-BF113). Not a ver informative question is it? What I meant was if (BF109<BF113) do you want to subtract (BF109-BF113) from BA95 or BA96+BA95+BA94? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... I'm so confussed right now I can't remember the third condition? You're confused? <g But you still don't say when you want to subtract (BF109-BF113). -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Brian" wrote in message ... Sandy, Hi! what I'm trying to do is set three conditions each with formula set to each condition. I'm thinking I don't need theis part of the existing formula (=IF(SUM(BJ94:BJ96)<BJ95,BJ95 and I can get rid of an if function The following are two of the three formulas that work independently. Formula if Condition BF109 BF113 exists! =IF(SUM(BA94:BA96)<BA95,BA95,BA96+BA95) Formula if condition BF109<BF113 exists! =IF(SUM(BA94:BA96)<BA95,BA95,BA96+BA95+BA94) I'm so confussed right now I can't remember the third condition? Thanks for your help! "Sandy Mann" wrote: That will of course subtract from either BJ95 or BJ95+BJ96 when ever BF113<BF109 is true which may not be what you want. Actually now that I take the time to read your formula: SUM(BJ94:BJ96) must always be equal to or bigger than BJ95 because BJ95 is contained withing the SUM() so the test must always be False! Did you mean SUM(BJ94,BJ96) which is BJ94 + BJ96? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... Or with fewer key stroks but with one more function call: =IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-MAX(BF109-BF113,0) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... Try: =IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-(BF109-BF113)*(BF113<BF109) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Brian" wrote in message ... I have this formula in a cell (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95+BJ94) . this formula does not function correctly in all conditions. In certain conditions it needs to run like this (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95). and in another condition it need to be this (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-(bf109-bf113) What I need to do is set a condition something like this =if(Bf113<bf109) choose a say this formula (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-(bf109-bf113) So the final question I have is! Is it possible to set say three conditions and have a formula and it choose a separate formula per each condition. in a single cell. |
Excell formula help
what I'm trying to do is set three conditions each with formula set to
each condition. I'm thinking I don't need theis part of the existing formula (=IF(SUM(BJ94:BJ96)<BJ95,BJ95 and I can get rid of an if function The following are two of the three formulas that work independently. Formula if Condition BF109 BF113 exists! =IF(SUM(BA94:BA96)<BA95,BA95,BA96+BA95) Formula if condition BF109<BF113 exists! =IF(SUM(BA94:BA96)<BA95,BA95,BA96+BA95+BA94) I know I am jumping into this thread kind of late, but I am confused by your first test in the above two IF statements. SUM(BA94:BA96)<BA95 Unless the sum of the contents of BA94 and BA96 are negative, your test will **always** be true. Why? Because you are including BA95 in the summation range. You test condition expands to this statement... BA94 + BA95 + BA96 < BA95 which is equivalent, when reduced, to this test expression... BA94 + BA96 < 0 Is this really what you are meaning to test? As for a three condition test, consider this... =IF(Condition1,TrueForCondition1,IF(Condition2,Tru eForCondition2,IF(Condition3,TrueForCondition3,Fal seForAllConditions))) Rick |
| All times are GMT +1. The time now is 05:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com