If Functions

[watson86]

I am trying to create a series of IF functions so that the cell will register a number based on the value of the previous cell. Basically something like this:
If S6>=30, then S7 should read 25
If S6 is between 31-59, then S7 should read 20
If S6 is between 60-75, then S7 should read 15
If S6 is between 76-89, then S7 should read 10
If S6 is between 90-100, then S7 should read 3

I have tried nesting the cells and using a variety of symbols, but still nothing is working it will give me the "25" value but nothing else.

I would greatly appreciate some help!

 

[FDibbins]

Welcome to the forum

I would do this with a small table, and use VLOOKUP...

	A​	B​	C​	D​
2​	30​	25​	70​	20​
3​	59​	20​
4​	75​	15​
5​	89​	10​
6​	100​	3​

D2=VLOOKUP(C2,$A$2:$B$6,2,1)

 

[Guru008]

Hi,

There is logical error in your formulae

S6>=30, then S7 should read 25 should be "S6<=30, then S7 should read 25" because if we use >= for value 25 then every next condition will be fulfil as all the condition have value greater than 30, thats why your formula is giving 25 everytime

 

[Dathoang16]

Hi
I think this formula may work

= lookup(S6,{30,31,60,76,90},{25,20,15,10,3})

 

[BarryL]

Hi
I think this formula may work

= lookup(S6,{30,31,60,76,90},{25,20,15,10,3})

I'm not sure but I think maybe the OP wants 25 for everything up to and including 30. If that's the case I'll amend your formula slightly

=LOOKUP(S6,{0,31,60,76,90},{25,20,15,10,3})

 

[watson86]

I'm not sure but I think maybe the OP wants 25 for everything up to and including 30. If that's the case I'll amend your formula slightly

=LOOKUP(S6,{0,31,60,76,90},{25,20,15,10,3})

I pasted this in the cell and it didn't work. Thank you.

 

[Dathoang16]

Does it show #N/A in the cell?

You can try this formula work

=LOOKUP(Value(S6),{0,31,60,76,90},{25,20,15,10,3})

 

[watson86]

So far, none have worked. They all return the first value of 25, but nothing else when the value in S6 changes.

 

[FDibbins]

Did you try my suggestion in post #2?

 

[watson86]

Did you try my suggestion in post #2?

I-don't-know-how-to-do-that-(thepostwontletmespace