error in formula

[Blackwell]

Hi Guys, I am trying to find the correct formula between the value for 40% tax. If C2 is between C3:C4, then that value goes to C5, if the value is less than C3 input 0 in C5.
C2 21,905
C3 6283
C4 20,857
C5 14,574
This is the formula I am using =IF(AND(C2>C3,C2:C4),IF(C2>C3,C4-C3),0). When the value is less in C2 it does not return "0"

C2 1,953
C3 3,142
C4 10,482
C5 TRUE
This is the formula I am using =IF(C2>C3,0)=IF(AND(B2>B3,B2:B4),IF(B2>B3,B4-B3),0) C5 is showing TRUE instaed of "0"
Thank you in advance.

 

[RoryA]

What value do you actually want in C5? The amount of C2 that falls between C3 and C4 (i.e. greater of 0 and C2-C3, but capped at C4), or the value of C2 if it falls in the range, or something else? For the former, MEDIAN would be the simplest option:

=MEDIAN(0,C2-C3,C4)

 

[Blackwell]

Hello RoryA, Thank you for replying. actually, no, what I want is If C2 is between C3 and C4, then the value between c3 and C4 which is 14,574 to go to C5. If C2 is less than C3 then in C5 "0". If C2 is higher than C4 then the value between C3 and C4 goes into C5. I hope that is understandable.

 

[RoryA]

In that case:

=MEDIAN(0,C2-C3,C4-C3)

 

[Blackwell]

Hi RoryA, sorry that is not working. It produces 7,287; it should be 14,574. I am not sure I have explained this well enough.
what I want is If C2 is more than C3 and within C4 then the value between c3 and C4 which is 14,574 goes to C5. The
If C2 is less than C3, then in C5 "0". If C2 is higher than C4 then the value between C3 and C4 goes into C5.

 

[RoryA]

It produces 7,287

No, it does not:

Book1
	C
2	21,905
3	6,283
4	20,857
5	14,574
Sheet1

Cell Formulas
Range	Formula
C5	C5	=MEDIAN(0,C2-C3,C4-C3)

 

[Blackwell]

I am so sorry you are right. It does work correctly, I tried it in a new sheet, and it is fine. I have made an error somewhere. But Thank you