Neg. Number

aapjrat5128

Board Regular
Joined
Apr 7, 2002
Messages
53
I have the following formula in E58
=IF(ISERROR(C58/D58),””,C58/D58)
My problem is that there is a number in
D58 but not in C58 and I get a negative number,
Is there any way that I can change the formula?
So that I can get a zero or blank.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
if c58 is blank, and d58 has a value, your formula should return 0 ?? care to describe the sorts of values in c58 & d58? are they the results of formulas or manually entered?

paddy
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
I remain unclear about wehat's going on in the cells, what you're trying to accomplish & why you think the formula isn't working. as I said, as writen the formula will return 0 if C58 is blank or zero & should function as expected with error values. I can't see where you get the negative issue from.

more info please.

paddy
 

aapjrat5128

Board Regular
Joined
Apr 7, 2002
Messages
53

ADVERTISEMENT

I am keeping track of mileage plus other things on many vehicles, I have the months
in column A, mileage in col.B, Difference in
mileage from month to month in C, days elapsed in the month D, Avg. miles per day in E.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
OK - but why is the formula a problem (sorry if I'm being dense here, but whiskey has that effect!). I still don't get what the problem is. care to post an example of the data you're using when the formula returns unexpected results. you might consider installing the html maker addin from the link below this post & posting up a snapshot of your spreadsheet.

paddy
 

aapjrat5128

Board Regular
Joined
Apr 7, 2002
Messages
53

ADVERTISEMENT

I have downloaded the HTML maker, but how do I use it to post the spreadsheet???
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
To install:

1) Quit excel
2) find out where your library folder is - do this by searching for *.xla files on your c drive.
3) copy the htmlmaker file to the library folder
4) Open excel
5) go to tools | addins. Select the HTMLMaker 2.20 option.

To use:

1) Select the range of cells you want to post. (In general, don't post more cells than you need to get the message across & avoid posting cells with lots of formatting - borders, fill colours etc)

2) go to html | convert sheet to html, hit OK twice
3) you should now have a new browser window opened with you're html'd sheet. Hit the "Please click this button to send the source to the clipboard" button.
4) When writing a post, just click paste (or control + V). the html will be pasted into your post. once you hit submit, the html will be rendered appropriately by the board.

paddy
 

aapjrat5128

Board Regular
Joined
Apr 7, 2002
Messages
53
Buick Milage for posting to web.xls
ABCDE
1DateMilesDiffDaysElapsedAverageMilesperday
28/27/200272,795.00   
39/27/200274,032.001,23731.0039.90
410/27/200275,112.001,08030.0036.00
511/27/2002(75,112)31.00(2422.97)
612/27/2002030.000.00
71/27/2003031.000.00
Sheet1
Buick Milage for posting to web.xls
ABCDE
1DateMilesDiffDaysElapsedAverageMilesperday
28/27/200272,795.00   
39/27/200274,032.001,23731.0039.90
410/27/200275,112.001,08030.0036.00
511/27/2002(75,112)31.00(2422.97)
612/27/2002030.000.00
71/27/2003031.000.00
Sheet1
 

jtfourh

New Member
Joined
Oct 30, 2002
Messages
9
=IF(ISERROR(C58/D58),””,C58/D58)
if you get a negative number from a division of two numbers, one of them has to be negative input, doesn't it?

Also, why don't you change the "if true" to "0" since I get an error when putting this formula as typed.
 

Forum statistics

Threads
1,144,770
Messages
5,726,186
Members
422,661
Latest member
foxleinlady

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top