Multiple Conditions - Greater than less than

[Scarebare]

Hi – I have a series of numbers is cells A1- D4. say 44, 16, 10, 5

I would like to be able to write a formula that returns the following answers.

1. If "A1" is between 10 and 20 then i want to be able to take the difference above 10 away from 10. ie If A1 is say 15 then i want to produce an answer that is '5" (10-(15-10)
2. For number greater than 20 I want to return a zero value.
3. If A1 is < 10, A1
4. If A1 =10, A1.

So in the case of the numbers i provided above in A1 would equal 0, B1 = 6, C1=10 & D1 = 5

 

[Scarebare]

Please ignore the above - solved - Thanks ChatGPT !!!

 

[Peter_SSs]

Welcome to the MrExcel board!

Please ignore the above - solved - Thanks ChatGPT !!!

Care to share that solution to assist other readers?

If "A1" is between 10 and 20 then i want to be able to take the difference above 10 away from 10.

So in the case of the numbers i provided above ... B1 = 6

Perhaps I have mis-interpreted what you wrote but that seems contradictory to me.
For the number 16, the difference above 10 to me is 6. If take that away from 10 you get 4, not 6. 10-(16-10)

 

[Scott Huish]

I agree that 4 should be correct for that.
Here is my formula anyway:
=IF(A1>20,0,MIN(A1,(10-(A1-10))))

Book1
	A	B	C	D
1	44	16	10	5
2	0	4	10	5
Sheet1

Cell Formulas
Range	Formula
A2:D2	A2	=IF(A1>20,0,MIN(A1,(10-(A1-10))))

 

[Peter_SSs]

I agree that 4 should be correct for that.

If it is correct then another option might be ..

24 03 04.xlsm
	A	B	C	D
1	44	16	10	5
2	0	4	10	5
Scarebare

Cell Formulas
Range	Formula
A2:D2	A2	=MAX(10-ABS(A1-10),0)

 

[Scarebare]

Welcome to the MrExcel board!


Care to share that solution to assist other readers?



Perhaps I have mis-interpreted what you wrote but that seems contradictory to me.
For the number 16, the difference above 10 to me is 6. If take that away from 10 you get 4, not 6. 10-(16-10)

Yes, you are correct - it was my bad.
Chatgp gave me this which works well: =IF(J13 > 20, 0, MAX(0, IF(J13 >= 10, 10 - (J13 - 10),J13)))

 

[Peter_SSs]

Chatgp gave me this which works well: =IF(J13 > 20, 0, MAX(0, IF(J13 >= 10, 10 - (J13 - 10),J13)))

Thanks for that. Although that works, you can see that there are considerably shorter options.

BTW, is it possible for the original number to be negative? If so, what should the result be (examples)?