Log In
Register
Home
September 18, 2019
Offline
Hello,
1. I need a formula for D column if A1 have any text and B1 and C1 is empty the D1 Should Should get 2000
2. If A1 and B1 have any text then D1 should get 4000
3. If A1, B1 and C1 have any text then D1 should get 6000
4. If A1 have specific text that is 2 stops then D1 should get 1000.
Note: =IF(E78<>"",IF(F78<>"",4000,2000),"")
I m using this formula for first two conditions and it is working correctly but i want to add condition # 3 & 4 in the same formula please guide me what to do???
| A | B | C | D |
| Any Text | - | - | 2000 |
| Any Text | Any Text | - | 4000 |
| Any Text | Any Text | Any Text | 6000 |
| 2 Stops | - | - | 1000 |
Waiting for respond on this and file of excel is also attached.
Regards
Waqas Munir
See AnswerNew Member
Power Pivot
May 26, 2016
Offline
Hello Waqas Munir
I have used the following formula to solve your problem:
=IF(A2="2 Stops", 1000, IF(C2<>"", 6000, IF(B2<>"", 4000, 2000)))
Instead of approaching your logical reasoning from top to bottom, I have evaluated the easiest argument first (If the value in column A2 = “2 Stops”, then return 1000, irrespective of the value of the cells in any other columns.)
Then I evaluated the rest of your process back to front. First check if value in column C is empty. If not, return 6000 (irrespective of values in other cells).
If value in column C is not empty, check if value in column B is empty, if not, return 4000, and if it is empty, it falls back to the last possibility: That only the value in column A contains text, in which case 2000 should be returned.
This solution assumes that there won’t be a value in one column if there isn’t a value in the column just before it. For example, it assumes that Column C will not be populated if Column A and B isn’t also populated with some text. If this is NOT the case, this formula might not work as intended, then you would need another solution / formula which may be longer / a bit more complicated.
Hope this helps.
September 18, 2019
Offline
Hello Carine Hough,
Formula is working for all conditions but....with this formula answer remains 2000 if A1 B1 C1 is empty whereas answer in D1 also should empty if all of the three are empty. Here is your suggested file is attached please see it. Just clear this error rest is ok and i am waiting for your respond.
Thanks in advance Carine.
New Member
Power Pivot
May 26, 2016
Offline
Hi Waqas
No problem, I have wrapped the formula in another IF function to deal with this condition too.
=IF(A2="", "", IF(A2="2 Stops", 1000, IF(C2<>"", 6000, IF(B2<>"", 4000, 2000))))
See attached file again - it now deals with all 5 conditions.
Answers Post
1 Guest(s)
