Which Function or combination?

[sophie2000]

For some reason, I've got a mental block today. Here's what I want to do:
Cell A1 = 100000
Cell A2 = 200000
Cell a3 = 150000 (this is the variable)

If cell A3 is in the range of A1-A2, then return a formula: (A3-A1) *.01
If cell A3 is greater than cell A2, the return a set number: 1000.

As a BONUS, I'd LIKE it to ALSO return a '0' if Cell A3 is less than 100000.

Just can't seem to get it right. Any help really appreciated!

Thank you !

Wendy

 

[FDibbins]

Maybe this?

=IF(A3<A1,0,IF(A3>A2,1000,(A3-A1)*0.01))

huh when I edit this, the formula shows...
=IF(A3 < A1,0,IF(A3>A2,1000,(A3-A1)*0.01))
But when I "save", the "<" is missing
edit: actually everything between the "<" and ">" is missing

 

[par60056]

Welcome to the forum.

There is probably a better way to do this but here is the simple solution.
=if(a3<=a1,0,if(a3>a2,1000,(a3-a1)*.01))

 

[sophie2000]

Ford - so easy....why didn't I think of that?? LOL Thank you!

 

[sophie2000]

Hey Guys - Similar situation. Here's the actual formula I'm using:

=IF($I$17<B18,0,IF($I$17>B18,(($I$17-B18)*F18),IF($I$17>C18),0)))

The first if statement is working: When I put in a value that is lower than B18, it returns a zero.
The second if statement is working: When I put in a value that is greater than B18, the calculation is done correctly.
The third if statement is NOT working. When I put in a value greater than C18, it returns the same as the second if statement. What I want it to do with this third statement is compare the value to another (c18) and if it is higher, return a 0.
I'm sure this is something really simple, but I'm just not seeing it.

Again, Thanks in advance!

 

[FDibbins]

I think I am having the same priblem with your formula in post # 5 as I had in post post # 2 - stuff missing

IF($I$17B18,(($I$17-B18)*F18),IF($I$17>C18),0)))

Assuming you are trying to build on my formula from post # 2 - and using my same cell refs from there...

=IF(OR(A3 < A1,A3 > C18),0,IF(A3>A2,1000,(A3-A1)*0.01))

 

[mikerickson]

The interface on this board has trouble with < and > (it thinks they are BB tags). Put a space on each side of the < and it should be fine.
Oddly enough <= works just fine.

 

[FDibbins]

Thanks for the feedback Mike, I started doing that and it seemed to work

 

[sophie2000]

tHANKS FOR that bit of info. WOw. I typed it in by hand and thought it did ok...

 

[sophie2000]

Ford - Yes. same problem. see post by mikerickson. It looks like what I'm looking for. I'll try it and get back to you

Wendy