Not sure "if"!

Not sure of a title for my problem and therefore I cannot check to see if it has been answered before in "FAQ's". I think it's related to an "IF" query.

My Problem!

I wish to formulate a cell (cell 2) that looks at the result of another cell (cell 1) to give an answer. If the result Cell 1 = <2 the answer needs to be "4". If the result in Cell 1 is between 2.25 and 3.75 the answer needs to be "3". If the result of Cell 1 is 4 the answer needs to be "0". The answers (4 3 and 0) will then be used to divide the sum in another cell.

Cell 1, is a formula cell which has an "Average" formula of the sum of cells above it. Due to the range of the numbers used (1 to 4) the average will always result in a range between 1 and 4 in divisions of 0.25.

Cell 2 must always result in either "4", "3" or "0" depending on the results of the three parameters ("<2", "between 2.25 and 3.75" and "4").

I think this should be a noddy question to those in the know, however my small excel brain will not compute this (seemingly) silly question.

Hope someone can help?

Try:

=IF(A1<=2,4,IF(A1<=3.75,3,IF(A1=4,0,"")))

where A1 is the your "cell 1"

Thanks NBVC, however "Cell 1" is D9 (in my table). So I changed all 3 "A1" to "D9" and the result was "4". When I changed the result of D9 to another number (to result between 1 and 4) the formula cell remained at "4" and didn't change to either "3" or "0".

Just to reiterate (assuming I've confused the issue!). If "D9" results in <2 then the answer I require is "4". If D9 results in any number between 2.25 and 3.75 then the answer I require is "3" and if D9 results in 4 (or greater than 3.75 to 4) then the answer I require is "0".

I copied and pasted your formula into the cell requiring the answer and only changed the "A1" to "D9" in the 3 places.

What if it is between 2 and 2.25, or greater than 4?

Hi Bob,

There are only three parameters. As follows: -

0 to <2.25 where the result needs to be 4;
>2.25 to <3.75 where the result needs to be 3;
>3.75 to 4 where the result needs to be 0.

The calculation I am using will not result in less than "0" or Greater than "4" (unless I've made an error - quite likely!)

I kind of assumed that you were jumping in steps of 0.25.

So my formula should then work, unless you are entering the numbers as TEXT.

Preformat the cell(s) as General or Number, then enter the number (this is better solution)

or, change formula to do it on the fly:

=IF(D9+0<=2,4,IF(D9+0<=3.75,3,IF(D9+0=4,0,"")))

Maybe

=LOOKUP(A2,{0,2.25,3.75},{4,3,0})

Was going to give that one too, but figured the IF() was better, with the assumption the OP is a beginner level.

Thanks guy's all suggested permutations (formulas) work. I will play with them and get the best one for my needs.