# My question stumpped my IT guy!

#### mug927

##### New Member
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!

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### Weaver

##### Well-known Member
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

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

#### mug927

##### New Member
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

##### New Member
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

##### Well-known Member
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)

Last edited:

#### mug927

##### New Member
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

##### Well-known Member
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

##### MrExcel MVP
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

##### MrExcel MVP
Either of these formulas in A4 gives a result of 31:
Code:
``=35+COUNTIF(A1:A3,">22")*22-SUMIF(A1:A3,">22",A1:A3)``

Code:
``=35+SUMPRODUCT(--(A1:A3>22),22-A1:A3)``
Is that what you're after?

Last edited:

Replies
4
Views
392
Replies
1
Views
79
Replies
1
Views
471
Replies
4
Views
571
Replies
1
Views
331

1,191,690
Messages
5,988,105
Members
440,126
Latest member
duque00

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back