Minus a value if sum is greater than

[MarcSwift]

What formula would I use if the total sum of a column was greater than 4.00 then I would minus 0.35. If not minus nothing.

Thankyou

 

[velkugan]

Hope this helps.....

Column A
1. 1
2. 1
3. 1
4. 1
5. 1
6. 1
7. 1
8. 1
9. 6.65 - Result

The formula is....

=IF(SUM(A2:A8)>4,SUM(A2:A8)-0.35,SUM(A2:A8))

 

[sykes]

=IF(SUM(H4:H8)>4,SUM(H4:H8)-0.35,SUM(H4:H8))

substitute the range references for yours.

 

[Aladin Akyurek]

What formula would I use if the total sum of a column was greater than 4.00 then I would minus 0.35. If not minus nothing.

Thankyou

Try...

=SUM(A:A)-IF(SUM(A:A)>4,-0.35,0)

 

[anandvarma]

try this

Excel Workbook
	A
1	1
2	2
3	1
4
Sheet1

 

[sykes]

Anandvarma
The OP wants the original sum as the result, if the result's less than 4.00 - your would return null in this case.

 

[sykes]

Aladin
I think you've got a double negative in there, so it will add .35!!

Should it be:
=SUM(A:A)-IF(SUM(A:A)>4,0.35,0)

.........perhaps?

 

[Aladin Akyurek]

Aladin
I think you've got a double negative in there, so it will add .35!!

Should it be:
=SUM(A:A)-IF(SUM(A:A)>4,0.35,0)

.........perhaps?

That's right... Thanks.

 

[MarcSwift]

That's great thank you!

I want to use this formula and add to this formula another logical statement if the sum is greater than 10 then minus 0.20. Where would I put this in the formula you just made?

Thanks you!!

 

[sykes]

It's always better to post the original FULL request in the very beginning (although I know what it's like - you just wonder if somethings possible, then once you realise it is, you know your formula may be able to do even more, so start expanding).........

However..
=IF(SUM(H4:H8)>10,SUM(H4:H8)-0.4, IF(SUM(H4:H8)>4,SUM(H4:H8)-0.35,SUM(H4:H8)))

.........should do the trick for you.

Knowing your actual requirement in the first instance may have resulted in a better (and more rounded) formula to suit all your needs, so this is an altered version of the original.

Hopefully you can see how to "Nest" the If statements to suit your needs.

Happy Xcelling!!