Need help with excel formula

August 30, 2011 at 12:50:38
Specs: Windows XP
I'm working on creating an excel formula to calculate a permit for the city. I need to have a formula that calcuates a sum of numbers, B12:B49, and multiplies it by a $1.60, which I have in G142. The problem I am having is that after $200 dollars, the muliplier changes to $1.07, in H142. There is also a minimum charge of $74.76, in I142, that needs to show up if not met.

Any and all information will be very helpful.

Thank you,
Patrick


See More: Need help with excel formula

Report •


#1
August 30, 2011 at 13:12:44
re: "The problem I am having is that after $200 dollars, the muliplier changes to $1.07, in H142."

When does the $200 limit come into effect, after SUM(G142) exceeds $200 or after SUM(B12:B49)*1.60 exceeds $200?

Basically what you need is a nested IF. Let's assume the $200 limit is based on the SUM alone.

Try this and let me know how it works:

=IF(SUM(B12:B49)>200, SUM(B12:B49)*H142, IF(SUM(B12:B49)*G142<I142, I142, SUM(B12:B49)*G142))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
August 30, 2011 at 13:32:26
I've been trying to get the formula to work with the IF function, I'm just not familiar enough with it yet.

The $200 limit comes into effect after SUM(B12:B49)*1.60 exceeds $200. After 200, you multiply by 1.07.

That's the part that is frustrating me. I tried what you had written before and it worked, so I think it's almost there.

Thank you.


Report •

#3
August 30, 2011 at 14:53:45
I was able to figure out most of it from the previous post, but I'm still having trouble with the min. amount, my I142 number. So far I have:

=IF(SUM(B12:B49)*G142<200,SUM(B12:B49)*G142,IF(SUM(B12:B49)*G142>200,((SUM(B12:B49)-125)*H142)+200,IF(SUM(B12:B49)*G142<H142,H142)))

This formula works for everything except for when my number does not meet the min. requirement.


Report •

Related Solutions

#4
August 30, 2011 at 15:34:02
Let's start with the fact that you never mentioned anything about subtracting 125 and then adding 200. The formula you posted may do most of what you want, but it sure doesn't match what you asked for in your original post.

I don't understand what you are doing in the final part:

IF(SUM(B12:B49)*G142<H142,H142

If H142 contains 1.07, why are you checking it against SUM(B12:B49)*G142?

With G142 containing 1.60, SUM(B12:B49) would have to be less than 0.668749375 for that part of the formula to be true.

Shouldn't you be comparing it to I142?

You are also going to have an issue if SUM(B12:B49)*G142 ever equals 200 since you only checking for < or > 200.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
August 31, 2011 at 06:50:30
You're absolutely right, it's was suppose to be I142, which still didn't work. I've also updated the <200 to say <=200, and that works out.

The reason I subtracted 125, then added 200 is b/c after SUM(B12:B49)*1.60 exceeds $200, you multiply the remaining amount by 1.07, H142.; 200/1.6=125.

So 125 x 1.6 = 200 (which is the max it can be)
Then, ((SUM(B12:B49)-125)*H142) = the remaining amount using the 1.07 mulitplier
Then, +200 to = total amount w/ both multipliers.

I'm sorry if I didn't make that very clear. I guess I should have said the multiplier changes after the first 200 is reached, but then the remaining amount is multiplied by 1.07.

Again, thank you for your help.


Report •

#6
August 31, 2011 at 13:54:22
re: "The reason I subtracted 125, then added 200 is b/c after SUM(B12:B49)*1.60 exceeds $200, you multiply the remaining amount by 1.07, H142.; 200/1.6=125. "

Either I don't undertstand what you are trying to say or you are doing that wrong.

When I read: "after SUM(B12:B49)*1.60 exceeds $200, you multiply the remaining amount by 1.07" I assume the "remaining amount" is:

SUM(B12:B49)*1.60 - 200

For example, just to use round numbers, let's say SUM(B12:B49) = 300.

300 * 1.6 = 480

Since this exceeds 200, the "remaining amount" is 280:

300 * 1.6 - 200 = 280

Therefore, it seems to me that you should be using this whenever SUM(B12:B49)*1.6 exceeds 200:

=(A1*1.6-200)*1.07+200

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question