Formula for success please

March 11, 2009 8:24 PM

I inherited an Excel spreadsheet at work that calculates our yearly fees for our customers. Too bad there's still too much hand figuring to be done. The inherited spreadsheet figures the rates up to a point but then I still have to "hand compare" two columns. I'm convinced Excel can do the work for me; I just don't know how to tell it what to do.

We charge our customers a flat rate plus a percentage up to a maximum of 10% of a certain amount of money they receive from an outside source. I know how to make excel figure both those amounts, no problem.

What I need to know is how to tell the spreadsheet to then pick from whichever amount is less, the 10% or the standard flat fee plus 3% and put that lesser amount into a third column that could then be summed. Which function do I use and EXACTLY how do I use it?

I am a beginner to intermediate level Excel user so be gentle with me please.

posted by tamitang to Computers & Internet (9 answers total)

Assume rate 1 is in A1 and rate 2 is in A2

In your output cell, put:

=IF (A1>A2;A1;A2)

(translated:if the value of A1 is greater than the value in A2, make this cell equal to A1. Otherwise, make it equal to A2)
posted by chrisamiller at 8:30 PM on March 11, 2009

Hrmmm - I can't remember what Excel uses as delimiters. If it doesn't work, replace the semicolons with commas.
posted by chrisamiller at 8:31 PM on March 11, 2009

I think you are looking for the SMALL function. Check this out.
posted by roomwithaview at 8:31 PM on March 11, 2009

Excel uses commas for delimiters. So, chrisamiller's example should be:

=IF(A1>A2,A1,A2)
posted by eschatfische at 8:36 PM on March 11, 2009

Do what Bahro said. As a teeny update, the "A1:A2" format is for a range of cells between those two (which in this case would just be those two, but "A1:A10" would be 10 cells). If you have data in two completely separate cells make them a comma separated list. So =MIN(B3,E9)
posted by markr at 8:45 PM on March 11, 2009

The MIN function will work great if you have those amounts in the sheet already on each line - and, as you have it, it sounds like you have a column for the 10% and a column for the flat fee + 3% amount. Just do =MIN(<1>, ) , replacing, of course, <> with cell references.

But you actually don't need those two columns - you can figure it in one formula. Say that cell A1 contains the amount a customer recieved, and y is the flat fee you charge:

=IF((0.1*A1)<>
or: "if 10% of the amount is less than 3% of the amount plus the flat fee, then charge the ten percent; otherwise, charge the 3% plus the flat fee."
posted by koeselitz at 8:58 PM on March 11, 2009

Sorry, I meant "do =MIN(<1>, <2>)."
posted by koeselitz at 8:59 PM on March 11, 2009

What the hell? Display got borked. Let's see if I can try that again:

=IF((0.1*A1)<(0.03*A1+y), (0.1*A1), (0.03*A1+y))

is the formula that should work.
posted by koeselitz at 9:03 PM on March 11, 2009 [1 favorite]

Response by poster: Thanks! That worked.
posted by tamitang at 9:15 PM on March 11, 2009