Results 1 to 15 of 16
Thread: Formula help

20150307, 07:31 #1
 Join Date
 Dec 2001
 Location
 Chandler, AZ
 Posts
 2,168
 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.
ThanksThanks John
Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

20150307, 07:58 #2
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,488
 Thanks
 376
 Thanked 1,472 Times in 1,340 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.
HTHLast edited by RetiredGeek; 20150307 at 08:00.
May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20150307, 10:24 #3
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,686
 Thanks
 121
 Thanked 667 Times in 608 Posts
Or =IF(AND(D143>0,F143>0),D143*16/F143/D143,"")
HTH
Maud

20150307, 10:50 #4
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,488
 Thanks
 376
 Thanked 1,472 Times in 1,340 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.
HTHMay the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20150307, 11:07 #5
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,480
 Thanks
 32
 Thanked 63 Times in 59 Posts
Another option is to use the CONVERT function:
convert.jpgLast edited by kweaver; 20150307 at 11:12.

20150307, 11:22 #6
 Join Date
 Dec 2001
 Location
 Chandler, AZ
 Posts
 2,168
 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
JohnLast edited by t8ntlikly; 20150307 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)

20150307, 11:34 #7
 Join Date
 Dec 2001
 Location
 Chandler, AZ
 Posts
 2,168
 Thanks
 46
 Thanked 13 Times in 11 Posts
Thanks kweaver! that's pretty neat but a bit heavy for this application I think
JohnThanks John
Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

20150307, 11:51 #8
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,946
 Thanks
 140
 Thanked 516 Times in 492 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

20150307, 12:17 #9
 Join Date
 Dec 2001
 Location
 Chandler, AZ
 Posts
 2,168
 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
JohnThanks John
Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

20150307, 13:05 #10
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,946
 Thanks
 140
 Thanked 516 Times in 492 Posts
..and why would that be any different?????
zeddy

20150307, 13:17 #11
 Join Date
 Dec 2001
 Location
 Chandler, AZ
 Posts
 2,168
 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)

20150307, 14:09 #12
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,488
 Thanks
 376
 Thanked 1,472 Times in 1,340 Posts
John,
How about this: =IFERROR(C2/(A2*16),"")
t8ntlikly.JPG
HTHMay the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20150307, 15:35 #13
 Join Date
 Dec 2001
 Location
 Chandler, AZ
 Posts
 2,168
 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 againThanks John
Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

20150307, 16:27 #14
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,946
 Thanks
 140
 Thanked 516 Times in 492 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

20150307, 16:59 #15
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,686
 Thanks
 121
 Thanked 667 Times in 608 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.