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

2. 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

3. Or =IF(AND(D143>0,F143>0),D143*16/F143/D143,"")

HTH
Maud

4. 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

5. Another option is to use the CONVERT function:

convert.jpg

6. 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

7. Thanks kweaver! that's pretty neat but a bit heavy for this application I think

John

8. 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. Thanks zeddy What I'm trying to get is the cost per ounce for the total amount purchased, not just for 1 pound

John

10. ..and why would that be any different?????

zeddy

11. because what. I want is. The cost of the package, not just the cost for a pound.

12. John,

t8ntlikly.JPG

HTH

13. 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

14. 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. 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 Last

#### Posting Permissions

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