Take the 2-minute tour ×

If A1 contains 0.3 I want B1 to say 1

If A1 says0.45 I want B1 to say 0

If it's anything in-between 0.3 and 0.45 I want it to say the proportional difference, so if A1 said 0.375, B1 should say 0.5 as its half way between 0.3 and 0.45.

Is is even possible? I'm thinking nested IFs but I can't see how to actually get there?

share|improve this question
add comment

4 Answers

up vote 7 down vote accepted

you could try this: =(0.45-A1)/(0.45-0.3)

Simplified: =(0.45-A1)/0.15

Another alternative: =3-A1/0.15

share|improve this answer
Sheer genious! I don't think it can be simplified further :D –  jwbensley Oct 28 '11 at 16:21
this is fine for the examples you have provided, but it is unbounded. Your examples cater for A1 being between 0.3 and .45, if its possible for A1 to lie outside this range then joshb's solution is more suited. –  brettdj Oct 28 '11 at 22:42
@brettdj The original poster did not specify the behavior for values outside the range 0.3 to 0.45, so I did not address that. It may be that the message "out of range" is desired in that case, but it may also be that the result of the given formula is the appropriate value. Or it may be something else entirely. –  phoog Oct 29 '11 at 3:58
@phoog. That's all correct. But still I'd rather see basic boundary handling as per joshb's approach, his answer covers the question concisely but it also makes it very clear how to address a value outside the specified range. So the OP could easily adjust it rather than have to ask a further clarifying question. –  brettdj Oct 29 '11 at 6:13
add comment

=IF(AND(A1 >= 0.3, A1 <= 0.45), ((0.45-A1)/(0.45-0.3)), "out of range")

share|improve this answer
+1 for considering the (unspecified) case where the value is not between 0.3 and 0.45. –  phoog Oct 28 '11 at 17:44
Oh yes, that is a good idea (although the interface controls wouldn't let this happen which I didn't mention, this is still very important though so thanks!). –  jwbensley Nov 2 '11 at 8:51
add comment


share|improve this answer
The IF functions aren't necessary. It's a bit like saying =IF(C3=1,1,IF(C3=2,2,C3)). –  phoog Oct 28 '11 at 16:20
You are quite correct. I really like your answer and +1'd it. –  SpectralGhost Oct 28 '11 at 16:26
add comment

this seem to work and looks easy.In B1

share|improve this answer
add comment

Your Answer


