Neg. Number

[aapjrat5128]

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.

 

[PaddyD]

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

 

[aapjrat5128]

C58 and D58 both have formulas that result in numbers.

 

[PaddyD]

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]

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]

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]

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

 

[PaddyD]

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]

Buick Milage for posting to web.xls
	A	B	C	D	E
1	Date	Miles	Diff	DaysElapsed	AverageMilesperday
2	8/27/2002	72,795.00
3	9/27/2002	74,032.00	1,237	31.00	39.90
4	10/27/2002	75,112.00	1,080	30.00	36.00
5	11/27/2002		(75,112)	31.00	(2422.97)
6	12/27/2002		0	30.00	0.00
7	1/27/2003		0	31.00	0.00
Sheet1

Buick Milage for posting to web.xls
	A	B	C	D	E
1	Date	Miles	Diff	DaysElapsed	AverageMilesperday
2	8/27/2002	72,795.00
3	9/27/2002	74,032.00	1,237	31.00	39.90
4	10/27/2002	75,112.00	1,080	30.00	36.00
5	11/27/2002		(75,112)	31.00	(2422.97)
6	12/27/2002		0	30.00	0.00
7	1/27/2003		0	31.00	0.00
Sheet1

 

[jtfourh]

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