Conditional Result in Excel

[Abdullah_h]

Hello guys

I need your help to write the correct formula. I will explain my concern in a simplified example:

CELL | Value

A1 | 500
A2 | =if(A1>100, "Error: Too High", "")
A3 | =if(A1<0, "Error: Too Low", "")


I need to write a formula in B1 that returns A1 only if A2 and A3 are both false. If any of A2:A3 is true, then return its value. In the aforementioned example, the B1 result should be "Error: Too High".

If we change the number in A1 to 99. B1 should show 99.

If I add another condition in A4 as follows: =if(A1<-10, "Error: Extremely Low",""). And add a number -11 in A1, how to show both errors in B1.

I hope my explanation is clear.

Thank you

 

[AlanY]

Re: Need Help - Conditional Result in Excel

try

=if(A1<-10,"Error: Extremely Low",if(A1<0,"Error: Too Low",if(A1>100, "Error: Too High",A1)))

 

[Abdullah_h]

Re: Need Help - Conditional Result in Excel

This would only help for a simple example. However, it is needed actually for a very long list of errors with very long formulas & it would be too difficult to combine all in one. I am thinking of using VLOOKUP function but could not write it correctly.

Hint: if this would help, all conditions include the text "Error".

 

[AlanY]

Re: Need Help - Conditional Result in Excel

try this, you can extend the vlookup table in Cols D:E as needed

Book1
	A	B	C	D	E
1	Error: Too Low	-5		-100	Error: Extremely Low
2				-10	Error: Too Low
3				0	OK
4				100	Error: Too High
5				500	Error: Too Too High
Sheet2

Cell Formulas
Range	Formula
A1	=IF(VLOOKUP(B1,D1:E5,2)="OK",B1,VLOOKUP(B1,D1:E5,2))

 

[Abdullah_h]

Re: Need Help - Conditional Result in Excel

Thanks Alan. However, I could not apply your formula

Allow me share with you another example. Hopefully you understand my concern better. How would you write the formula here in F16 that should return H11 unless if one of the exemptions apply (then it should show the error instead of H11). Please have a look at the uploaded picture (unfortunately I could not insert the pic in the post

).

https://imgur.com/a/0IMGJZU

Thank you in advance,

 

[Abdullah_h]

Re: Need Help - Conditional Result in Excel

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]XY Company
[/TD]
[TD][/TD]
[TD="align: right"]Date
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Client Name
[/TD]
[TD][/TD]
[TD="align: right"]Location
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Delivery Date
[/TD]
[TD][/TD]
[TD="align: right"]Valuation
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Total Price
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Cell B7 should combine all these formulas:
=if(D1>B5, "Error: Purchase Date must be on/before Delivery Date", D5)
=if(D3="New York","Error: Location is not covered", D5)
=if(D5<0, "Error: Price is invalid", D5)
=if(B3="","Error: Client Name is missing", D5)

--------------------------------------------------

I tried to use the forum embedded tool to create the above table. I hope the idea I am trying to explain is now clear.

Thanks

 

[AlanY]

Re: Need Help - Conditional Result in Excel

that should cover them, you might wanted to tidy up the format

=IF(OR(D1>B5,D3="New York",D5<=0,B3=""),IF(D1>B5, "Error: Purchase Date must be on/before Delivery Date", " ")&IF(D3="New York","Error: Location is not covered", " ")&IF(D5<=0, "Error: Price is invalid", " ")&IF(B3="","Error: Client Name is missing", " "),D5)

 

[Abdullah_h]

Re: Need Help - Conditional Result in Excel

that should cover them, you might wanted to tidy up the format

=IF(OR(D1>B5,D3="New York",D5<=0,B3=""),IF(D1>B5, "Error: Purchase Date must be on/before Delivery Date", " ")&IF(D3="New York","Error: Location is not covered", " ")&IF(D5<=0, "Error: Price is invalid", " ")&IF(B3="","Error: Client Name is missing", " "),D5)

Still I don't want to write one formula gathering all conditions. In the real example, there are more than 50 conditions & it is impossible to rely on one if formula. I would prefer to add those conditions in a separate sheet where it return the error message if correct (condition is met) and empty cell if false. Then, write a simple formula to look for any errors if exist. If any error exists, then the vlookup should return that error otherwise shows the price.

 

[Abdullah_h]

Re: Need Help - Conditional Result in Excel

Still I don't want to write one formula gathering all conditions. In the real example, there are more than 50 conditions & it is impossible to rely on one if formula. I would prefer to add those conditions in a separate sheet where it return the error message if correct (condition is met) and empty cell if false. Then, write a simple formula to look for any errors if exist. If any error exists, then the vlookup should return that error otherwise shows the price.

Let's say the all errors are written in A1:A50 in Sheet2. The order I want to make is =IF(Sheet2!A1:A50<>"", look for the text(s) in this range & reflect it, otherwise show D7 which is the valuation)

 

[Abdullah_h]

Re: Need Help - Conditional Result in Excel

Good evening

After so many attempts, I have finally found out the correct formula. I will write it here for other people to avail.

1) First of all: I made a special sheet for conditions. Let's name it Sheet2.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]=if(b1<>"",1,2)[/TD]
[TD]=IF(Sheet1!B1>Sheet1!B2,"Error: Invoice Date Must be on/before Delivery Date","") [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]=if(b2<>"",1,2)[/TD]
[TD]=IF(Sheet1!B3="New York","Error: Location is not covered","")[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]=if(b3<>"",1,2)[/TD]
[TD]=IF(Sheet1!B4="","Error:Client Name is missing","")[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]=if(b4<>"",1,2)[/TD]
[TD]=IF(Sheet1!B5<0,"Error: Price is invalid","")[/TD]
[/TR]
</tbody>[/TABLE]


2) Write the following formula in the final price cell B7: =IFERROR(VLOOKUP(1,Sheet2!A1:B4,2,FALSE),D5)