My question stumpped my IT guy!

[mug927]

How would I go about writing a formula if I wanted to say "if any of the values in cells A1, A2, or A3 are greater than 22, subtract that difference from the number already in A4 (which is 35)
My help desk is no help!

 

[Weaver]

try this

=IF(MAX(A1:A3)>22,A4-MAX(A1:A3),"")

you didn't say what you wanted if the condition wasn't met, so I'm returning an empty string

 

[Scott Huish]

What if you have more than one number that is greater than 22?

 

[mug927]

It's close but something is still off. I'm getting a circular reference message, which I'm still not sure what that means. Appreciate the help though!

 

[mug927]

Oh I forgot, the value if false would remain 35. only if the other cells are greater than 22 would the difference be subtracted

 

[Weaver]

where are you copying the formula to?

I hope it's not to any of the referenced cells (A1:A4)

Try it in A5

=IF(MAX(A1:A3)>22,A4-MAX(A1:A3),A4)

If you want to use the formula in A4, then you'll need to hard code the value 35 thus:

=IF(MAX(A1:A3)>22,35-MAX(A1:A3),35)

 

[mug927]

Closer! I'm not getting an error message now at least. However this is my problem:
A1=22
A2=23
A3=25

A4=10

A4 should be equaling 31 since A2 and A3 were over 22

 

[Weaver]

Sorry, but if A4 has a value in it, you're not going to be able to influence that value with a formula in another cell. If that's what you're after, you're going to need visual basic, and I think that might be overkill.

Maybe you could put the formula in A5 and hide row 4, then row 5 will print/appear on screen in row 4's place

 

[Scott Huish]

And how are you getting 31 from 10?

If you are adding the 23+25 and subtracting 10 that would give you 38.

If you were subtracting all the numbers greater than 22 from 10 that would give you (i.e. 10-23-25) that would give you -38.


If you are subtracting 23 from 25 and then subtracting from 10 that would give you 8.

What is the math involved?

 

[Colin Legg]

Either of these formulas in A4 gives a result of 31:

=35+COUNTIF(A1:A3,">22")*22-SUMIF(A1:A3,">22",A1:A3)

=35+SUMPRODUCT(--(A1:A3>22),22-A1:A3)

Is that what you're after?