if then issues

1. if then issues

I am moderately familiar with excel functions, but I am trying to create a variable formula. Our product has a certain amount of minimal cost which reflected in our smallest unit. as the product gets bigger the marginal costs go down. that is to say the first 30 sq ft or 4320 sq inches cost out at \$.208333 per sq inch. larger sizes cost \$.16 per sq inch.
We also have an option that adds \$40.00, so I have that as the (AC32*40).
I have been trying =IF((AE32*AG32)>4320,(AE32*AG32)*0.16)+209+(AC32*40)

=IF((AE32*AG32)>4321,(AE32*AG32)*0.20833)+209+(AC32*40)

and this =SUM((AE22*AG22)*0.20833)+(AC22*40)

all of them work. but I need to be able to say, if((AE32*AG32)>4321,(AE32*AG32)*0.20833)+209+(AC32*40),((AE32*AG32)>4320,(AE32*AG32)*0.16)+209+(AC32*40)

when I add the two formulas I get errors. any idea why?

2. Re: if then issues

Maybe:
``Please Login or Register  to view this content.``

3. Re: if then issues

Thank you! for some reason, it does not let me use both "=" signs. it works, but has the same problem my current working formula has, it does not adjust down to .16 as a multiplier on amounts over 4320

4. Re: if then issues

The double "=" was a type-o.
Need to change the test for size:
``Please Login or Register  to view this content.``

5. Re: if then issues

sadly the suggested formula doest work except numbers less than 4320.

I am trying these, and they work perfectly, but only for the front half of either formula, they still don't do both:

"=IF((AE29*AG29)<4321,(AE29*AG29)*0.20833)+(AC29*20)*OR((AE29*AG29)>4320,(AE29*AG29)*0.16)+(AC29*20)"
"=IF((AE21*AG21)>4320,(AE21*AG21)*0.1615)+228+(AC21*20)*OR((AE21*AG21)<4321,(AE21*AG21)*0.20833)+(AC21*20)"

6. Re: if then issues

Are you trying to say that you want to subtract 4320 sq in from the actual size and charge \$0.20833 then charge @ \$0.16 /sq in for the remainder which is excess of 4320?
Given 5000 sq in then
(5000 - 4320) * 0.16 = \$108.80
4320 * 0.20933 = 899.99
total cost = 1008.79 (+ \$40 if option selected)

7. Re: if then issues

Thank you!

we sell shutters in custom sizes, because of the framing and basic labor, we charge 20.8333 cents per square inch for any shutters whose total surface is 4320 sq in or less. above that size we charge 16 cents per sq inch

so yes, except, when the shutter is say 2000 sq in, I need to charge the .20833. I need one formula that works for both large and small shutters

8. Re: if then issues

I think what Ben was asking, was if you have 5320 would you charge 20.8333 for the 1st 4320 and then 16 for the next 1000 or 16 for the full amount?

9. Re: if then issues

the first 4320 are at .20833, regardless of the total of the shutter. the .16 is for any balance over that, if there is any overage

10. Re: if then issues

I think that this should be close to calculating the price. It calculates both conditions of >4320 and <4320 as well as I understand your description. You don't say what triggers the value in AC32 only that it is added on.

This could be the basis of a solution for you.

Formula:
`Please Login or Register  to view this content.`

11. Re: if then issues

Thanks Ford, newdoverman, I was about to call in the Cavalry.
``Please Login or Register  to view this content.``

12. Re: if then issues

thanks all! will try both in the morning and report back!!

13. Re: if then issues

Folding Shutter Template with Formula with excel forum.xls

both formulas are returning the same answer, no matter the question. \$1114.39, that is true for a 36 x 36, 60 x60, 156 x 120.....

14. Re: if then issues

@ protonLeah

Does the Cavalry still exist?? LOL I had to read and re-read the problem and still wasn't sure. Now, to look at the latest upload.

15. Re: if then issues

Using the formula that I posted, if correct, it returns the following for the sizes that you quote:

36 x36......\$310.00
60 x 60.....\$790.00
156 x 120..\$3244.00

Formula:
`Please Login or Register  to view this content.`

The above formula produces the results in column AK

 AE AF AG AH AI AJ AK 18 WIDTH X HEIGHT PRICE Correct Pricing 19 36 36 789.99 \$310.00 \$310.00 20 60 60 789.9988 \$790.00 \$787.92 21 90 90 \$1,730.60 \$1,567.00 \$1,547.20 22 144 120 \$3,029.20 \$3,065.00 \$3,020.00 23 156 120 789.99 \$3,304.00 \$3,248.80 24 324 144 \$7,806.82 \$7,797.00 \$7,717.60 25 468 144 \$11,157.62 \$11,159.00 \$11,037.28 26 \$0.00 27 90 90 \$40.00 \$1,547.20 28 90 144 \$80.00 \$2,326.24 29 36 36 \$311.00 \$310.00 30 36 36 \$538.00 \$310.00 31 36 36 \$310.00 \$310.00 32 60 60 \$268.00 \$790.00

16. Re: if then issues

The formula you posted referenced row 32; however, your worksheet calcs start at row 19!

Therefore:
``Please Login or Register  to view this content.``
and drag down.

17. Re: if then issues

I can't see how the "correct" pricing was arrived at. Rounding might figure into it somehow. I didn't round until the final figures. That can make a big difference especially where the areas are large.

18. Re: if then issues

My calculation resulted in four decimal places (because of 0.208333) so I rounded to two. I was thinking in terms of dollars and cents, though ... the round is not necessary.

19. Re: if then issues

wow! thank you! it works!!!!!!

20. Re: if then issues

Thanks to all who helped before. I am currently using the following:

=IF(AE20*AG20<1575,AE20*AG20*0.2809,(AE20*AG20-1575)*0.17252+1575*0.2809)

to solve my matrix pricing issue, but it only works up to a point, is there a way to step down my rate again, so that for very large areas, my price goes down? I have these sizes and prices. my formula works well up to about 9000 square inches, and then it gets higher than my price is supposed to be.
Actual Prices Width Height Macro Generated Prices
\$364.00 36 x 36 \$364.05
\$791.00 60 x 60 \$791.77
\$1,065.00 72 x 72 \$1,065.04
\$1,739.00 96 x 96 \$1,760.64
\$2,577.00 120 x 120 \$2,654.99
\$3,579.00 144 x 144 \$3,748.07

I can stand the prices being a few dollars higher or lower, but as you can see my 144 x 144 is over \$175 over priced.

Also, is there a way to find the exact numbers in an equation like this, other than trial and error?

21. Re: if then issues

Perhaps you should create a new thread for this and upload a workbook showing the problem as it is obvious that the problem has substantially changed since the original thread posting.

22. Re: if then issues

will do, thanks

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1