excel if then condition, use remainder

Microsoft Excel 2004 for mac
May 12, 2010 at 10:49:57
Specs: Macintosh
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.


See More: excel if then condition, use remainder

Report •

#1
May 12, 2010 at 10:58:23
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
While

A3 1000

A8 1000

A12 5000

Will result in a blank cell.


MIKE

http://www.skeptic.com/


Report •

#2
May 12, 2010 at 11:07:41
excellent!!! thank you so much!

Report •

#3
May 12, 2010 at 11:09:13
yes, thanks for that additional note.

Report •

Related Solutions

#4
May 12, 2010 at 11:09:29
Curious, which way are you doing it?

MIKE

http://www.skeptic.com/


Report •

#5
May 12, 2010 at 12:10:49
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?


Report •

#6
May 12, 2010 at 12:37:12
That is the way the OP posted his question:

A3 - A8 - A12 = 700

If 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

http://www.skeptic.com/


Report •

#7
May 12, 2010 at 13:34:15
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.


Report •

#8
May 12, 2010 at 14:00:46
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

http://www.skeptic.com/


Report •

#9
May 12, 2010 at 16:17:37
i did this:

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


Report •

#10
May 12, 2010 at 16:23:31
I see no reason for using the =MIN() function.

MIKE

http://www.skeptic.com/


Report •

#11
May 12, 2010 at 16:43:26
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!

Report •

#12
May 12, 2010 at 18:37:17
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_false argument:

=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...


Report •

#13
May 12, 2010 at 20:51:07
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!


Report •

Ask Question