# 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

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

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 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 mulitplierThen, +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 - 200For example, just to use round numbers, let's say SUM(B12:B49) = 300.300 * 1.6 = 480Since this exceeds 200, the "remaining amount" is 280:300 * 1.6 - 200 = 280Therefore, it seems to me that you should be using this whenever SUM(B12:B49)*1.6 exceeds 200:=(A1*1.6-200)*1.07+200Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •