+ Reply to Thread
Results 1 to 22 of 22

if then issues

  1. #1
    Registered User
    Join Date
    04-02-2014
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    30

    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. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,064

    Re: if then issues

    Maybe:
    Please Login or Register  to view this content.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    04-02-2014
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    30

    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. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,064

    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.
    Attached Files Attached Files
    Last edited by protonLeah; 04-03-2014 at 02:44 PM.

  5. #5
    Registered User
    Join Date
    04-02-2014
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    30

    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. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,064

    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. #7
    Registered User
    Join Date
    04-02-2014
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    30

    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. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,310

    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?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  9. #9
    Registered User
    Join Date
    04-02-2014
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    30

    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. #10
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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: copy to clipboard
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  11. #11
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,064

    Re: if then issues

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

  12. #12
    Registered User
    Join Date
    04-02-2014
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: if then issues

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

  13. #13
    Registered User
    Join Date
    04-02-2014
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    30

    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. #14
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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. #15
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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: copy to clipboard
    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
    Last edited by newdoverman; 04-04-2014 at 09:31 AM.

  16. #16
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,064

    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.
    Last edited by protonLeah; 04-04-2014 at 04:25 PM.

  17. #17
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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. #18
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,064

    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. #19
    Registered User
    Join Date
    04-02-2014
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: if then issues

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

  20. #20
    Registered User
    Join Date
    04-02-2014
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    30

    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?
    Last edited by uplandpoet; 07-23-2014 at 04:01 AM.

  21. #21
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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. #22
    Registered User
    Join Date
    04-02-2014
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: if then issues

    will do, thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Data Validation List Issues/Sorting Issues
    By taylorbe in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-12-2014, 09:53 AM
  2. If Issues
    By Smulzeys in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-16-2013, 10:43 AM
  3. #REF Issues
    By kendo2748 in forum Excel General
    Replies: 2
    Last Post: 05-22-2009, 09:18 AM
  4. Changes & Issues
    By BrianBush in forum Excel General
    Replies: 1
    Last Post: 09-25-2007, 04:54 AM
  5. [SOLVED] C# VBA DLL issues
    By Temporalis in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-12-2006, 11:25 AM

Bookmarks

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