Hi. I'm trying to calculate a cell to check the sum of several cells, and if that number is greater than 500, to put the remainder in the cell. e.g., if A3-A8-A12 > 500, put the amount over 500 in A1 (so if A3-A8-A12 = 700, A1 would be 200)

I would greatly appreciate any help--I've looked at many forums, but I don't know exactly what to search on; 'remainder' is not turning up what I'm looking for.

Thanks.

You had it pretty much prototyped.

~~=IF(SUM(A3,A8,A12)>500,SUM(A3,A8,A12)-500,"")~~Just noticed you wanted it subtracted:

=IF(SUM(A3-A8-A12)>500,SUM(A3-A8-A12)-500,"")

You do understand that doing it as A3 minus A8 minus A12 means that A3 must be the largest number.

A3 5000

A8 1000

A12 1000

This will result in a remainder of 2500

WhileA3 1000

A8 1000

A12 5000

Will result in a blank cell.

MIKE

excellent!!! thank you so much!

yes, thanks for that additional note.

re: " You do understand that doing it as A3 minus A8 minus A12 means that A3 must be the largest number."Why do you say that?

That is the way the OP posted his question:

A3 - A8 - A12 = 700If A3 = 100 and A8 = 200 you get negative -100, if you then minus A12 = 100 you get negative -200.

Is there some permutation where A3 does not have to be the largest number?

MIKE

re: Is there some permutation where A3 does not have to be the largest number?Sure...

The goal was:

If A3-A8-A12 > 500, put the amount over 500 in A1.

The OP didn't say what to put in A1 if the result was <=500, so "nothing" seems like a fine choice.

Using the IF statement that you suggested (which I agree with!) if

A3-A8-A12 = 499 "nothing" would go into A1, right?OK, so what happens if A3-A8-A12 = - 499?

It's still less than 500, so "nothing" goes in A1.

Just so we're on the page:

I agree with your solution.

I just don't agree that "A3 must be the largest number". I don't think we really care since once we go below >500, the IF evaluates to "nothing".

In other words, anything between 500 and Negative Infinity are all the same to the IF statement.

I was just trying to make the point that if the OP expected any answer other than "nothing", then A3 would have to be the largest number. Wanted to make sure the OP understood the consequences of his question.

MIKE

i did this: =MIN(IF(SUM(A3-A8-A12)>500,SUM(A3-A8-A12)-500,0))

i got a "FALSE" result in the cell, so i tried the MIN function and it worked so i left it...but i don't remember if i had the formula exactly right at that point or not (it could have been when it was "" [empty quotes] and not 0 at the end)...i'll play around with removing it again.

thanks for all the input!

Either there i something you aren't telling us, or as Mike says, the MIN function does absolutely nothing. In fact, even without it, there is no way for this formula to return FALSE.

=IF(SUM(A3-A8-A12)>500,SUM(A3-A8-A12)-500,0)

The only ways to get a FALSE from an IF statement is to either "request" it:

=IF(A1<2,TRUE,FALSE)

or to leave out the

value_if_falseargument:=IF(A1<2,TRUE)

Since your formula has both arguments satisfied, it's either going to return a 0 or the remainder above 500.

Therefore the MIN is going to look like one of these:

=MIN(0)

=MIN(the remainder value)The MIN of a single value is the value itself.

Try this:

1 - Select the cell with the formula in it.

2 - Click on Tools...Formula Auditing...Evaluate Formula.

3 - Click the Evaluate button to single step through the formula.This should help you understand what each piece of the formula is doing and hopefully you'll see why Mike and I say the MIN function serves no purpose.

Unless there's something you're not telling us...

no, no secrets...i had originally started with a min statement, then i decided to go a different route, so--not being a formula whiz--i resorted to it when the above formula didn't work...but then it did. thanks for all the help!

Ask Your Question

Weekly Poll