Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Formula help

  1. #1
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Formula help

    The formula =IF(D143>0,D143*16)/F143/D143,
    What is happening is that if there is no number in D143, I get the good ole #DIV/0 error If there is a number of course the formula works great

    How can I get rid of that

    FWIW I am converting # to ounces so the cost comes out per oz.

    Thanks
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    t*ntlikely,

    There may be a better formula but this one seems to work and it also checks to see if F143 is >0 so you don't get divide by 0 error from it.
    =IF(OR(IFERROR(D143<=0,TRUE),IFERROR(F143<=0,TRUE) ),"",(D143*16)/F143/D143)

    Of course, you can but an error value (e.g. ERROR) between the "" if you wish.

    Also if you will have negative numbers and want to do the division get rid of the < signs.

    HTH
    Last edited by RetiredGeek; 2015-03-07 at 08:00.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Or =IF(AND(D143>0,F143>0),D143*16/F143/D143,"")

    HTH
    Maud

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud & t8ntlikly,

    I hurried off to tennis and was thinking while on the court (slow game) and I thought there had to be an easier way. Maud's that's a nice bit of code. I also thought that the way I was using IFERROR seemed awkward at best. So I looked up the help on IFERROR and guess what I was chasing the horse around the barn with the door open!

    =IFERROR((D143*16)/F143/D143,"") works just fine!

    IFERROR returns the value of the formula in argument one if it is NOT in error and the value in the second argument if it is in error. Seems much more logical.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Another option is to use the CONVERT function:

    convert.jpg
    Attached Images Attached Images
    Last edited by kweaver; 2015-03-07 at 11:12.

  6. #6
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts
    Hey Guys
    Both formulas work in getting rid of the #DIV0 errors etc. however the price per ounce is coming back in dollars and is way off. If I do it using separate cells it works like this, which is what I am trying to get into a single formula:


    Cost/# * Number of # (5# @ 6.29/# =$31.45)
    Number of #'s * 16 (5# * 16oz. = 80oz.)
    Total Cost (from step1)/Total Ounces (from step 2) ($31.45/80 =$.393/oz. < answer I need
    Here is a screen shot


    Screenshot (9).png


    Thanks
    John
    Last edited by t8ntlikly; 2015-03-07 at 11:29. Reason: Remove Screenshot
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  7. #7
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts
    Thanks kweaver! that's pretty neat but a bit heavy for this application I think

    John
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Blimey

    If you want the cost per ounce, just divide the cost per pound by 16.

    in cell [G141], put this formula:
    =F141/16
    ..that gives you the cost per ounce.

    If you want to copy the formulas down column [G] and don't want zeros, use this formula in [G141]
    =IF(F141="","",F141/16)

    zeddy

  9. #9
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts
    Thanks zeddy What I'm trying to get is the cost per ounce for the total amount purchased, not just for 1 pound

    John
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    ..and why would that be any different?????

    zeddy

  11. #11
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts
    because what. I want is. The cost of the package, not just the cost for a pound.
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  12. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    John,

    How about this: =IFERROR(C2/(A2*16),"")
    t8ntlikly.JPG

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #13
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts
    Thanks, RG, Maudibe, kweaver, zeddy,

    I stood back and tried a few different scenarios. Some almost worked but not quite as I kept getting errors. I used the fx feature and using cut/paste replaced things till it came out correctly here is the final answer =IF(D141<1,"",(F141*D141)/(D141*16))
    I'm sure there may be another way, but!



    Screenshot (10).png


    Thanks again
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  14. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    Just for the record, (F141*D141)/(D141*16) is mathematically equivalent to (a*b)/(b*16), and this is, of course, just a/16 (when you divide top and bottom by the same thing, b.
    So, have another look at my formula in post#8, and tell me that it gives the same answer. Please!
    Rory - help me out here! Maud - help me out here! RG is on the right track if you've got the total package cost and the weight in pounds.
    But, if you already have the cost as 'cost per pound', I can assure you that the cost per ounce is definitely (cost per pound)/16

    zeddy

  15. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    I was just following the OPs original formula. It didn't matter what the value of D143 was as it had no effect on the answer. As Zeddy states, a factor in the denominator will cancel the same factor in the numerator equal to 1.

Page 1 of 2 12 LastLast

Posting Permissions

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