Need help in solving the below scenario

[Chitti409]

I am trying to write a formula for the below conditions
B1 value varies based on that I need to get output in C1 using below condition
If B1>70 C1 should be 40
If B1>0 & <70 C1 should be 20
If B1<0 C1 should be 0
If B1=Exempted C1 should be Null
If B1=Not Reached C1 should be Null

 

[BlueHornet]

Welcome to the board.

This will not be a particularly difficult formula to write, and I'd be happy to help you do that, but don't forget the inflection points = 70 and 0. You address ">70" and "<70", and the same with 0, but you don't say what happens at B1 = 70 or B1 = 0.

Also, it's not exactly clear what B1 "exempted" means. Null? N/A? "Blank"? "Space"? Error value?

Ditto "not reached" for B1. What does that mean in terms of evaluating the cell?

 

[Logit]

.
As the question was posted :

=IF(B1<0,"0",IF(B1="Exempted","Null",IF(B1="Not Reached","Null2",IF(B1>70,"40",IF(AND(B1>0,B1<70),"20","")))))

Blue Hornet raises several valid points.

 

[jtakw]

Hi,

Try this:

Book1
	B	C
1	-1	0
2
3	0	20
4	Exempted
5	69	20
6	Not Reached
7	70	40
Sheet675

Cell Formulas
Range	Formula
C1	=IF(OR(B1={"Exempted","Not Reached",""}),"",IF(B1>=70,40,IF(B1>=0,20,0)))

 

[Chitti409]

Thanks for the reply..!

 

[Chitti409]

In addition to the above scenario i have to include the below scenario into the below formula how can i do it.

If B1="" it should become Not applicable and C1 should be ""

=IF(B1<0,"0",IF(B1="Exempted","Null",IF(B1="Not Reached","Null2",IF(B1>70,"40",IF(AND(B1>0,B1<70),"20","")))))

 

[Chitti409]

Thanks for the reply..!

 

[Logit]

.
You are welcome.