Minus a value if sum is greater than

V

velkugan

Board Regular

Joined

Dec 5, 2009

Messages

55

• Jul 14, 2011

• #2

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))

 

Upvote 0

sykes

Well-known Member

Joined

May 1, 2002

Messages

1,885

Office Version

1. 365

Platform

1. Windows

• Jul 14, 2011

• #3

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

substitute the range references for yours.

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Jul 14, 2011

• #4

MarcSwift said:

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

Click to expand...

Try...

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

 

Upvote 0

A

anandvarma

Active Member

Joined

Apr 26, 2011

Messages

384

Office Version

1. 365

Platform

1. Windows
2. MacOS

• Jul 14, 2011

• #5

try this

Excel Workbook
	A
1	1
2	2
3	1
4
Sheet1

 

Upvote 0

sykes

Well-known Member

Joined

May 1, 2002

Messages

1,885

Office Version

1. 365

Platform

1. Windows

• Jul 14, 2011

• #6

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.

 

Upvote 0

sykes

Well-known Member

Joined

May 1, 2002

Messages

1,885

Office Version

1. 365

Platform

1. Windows

• Jul 14, 2011

• #7

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?

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Jul 14, 2011

• #8

sykes said:

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?

Click to expand...

That's right... Thanks.

 

Upvote 0

M

MarcSwift

New Member

Joined

May 21, 2011

Messages

23

• Jul 14, 2011

• #9

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!!

 

Upvote 0

sykes

Well-known Member

Joined

May 1, 2002

Messages

1,885

Office Version

1. 365

Platform

1. Windows

• Jul 14, 2011

• #10

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!!

 

Upvote 0