Need a formula that picks the cell with a number and divides it by 18

[innkeeper9]

I'm still on windows 7 with office 2012 which gives me Excel 2007.

I have 4 columns, C has a time, D has a pre-meal glucose number, E has a post meal number and F converts those mmol/L numbers to mg/dl.

The D or E columns will have a number, never will both have a number in the same row i.e. if D5 has a number E5 won't. In Column F I need a formula that divides whichever cell has the number by 18. This is the way I did get it to work, for example:

F5 has =SUM(D5,E5)/18

That seems simple and it works and maybe that is the way to do it. However, because I need 1 place after the decimal for the mg/dl conversion, if there is no number in either the D or E column of a row in the F column a little dash appears in the cell. I have no idea why, or maybe it isn't the formula, but I can't figure out what else would do it.

Thanks for any help.

Ruth

[Glenn Kennedy]

It's almost certainly a zero, that has been formatted to look like a dash. What do you want to see? Then read the yellow banner (top of page) and post a small sample sheet.

[Pepe Le Mokko]

It is probably a custom formatting applied to the cell ?
Perhaps post a sample sheet ( see yellow banner)

[innkeeper9]

Thank you for your quick response. Sorry it took me so long to make a sample, but something nice happened,
though I have no idea why. When I was trying to make the sample, somehow I fixed the issue with that little dash.
Not only is it not there anymore, but no 0 is there either and that fixed everything even the last cell in Column
G which wouldn't correctly average because it would add all cells including the ones with that dash and no number.
But, then it would divide using all the cells which made an incorrect average. Now, it only adds the cells with
numbers and divides correctly.

However, the formula I was using to convert the glucose numbers to HbA1c was incorrect. I found the correct one
and I am using that. It seems to work, but I'm not sure the way I did it is the best way, so if anyone would
check it for me I would be greatly appreciative. It is =SUM(E5,F5,46.7)/28.7 and begins at G5

[Glenn Kennedy]

The formula is fine... I would tweak it ever so slightly:

=IF(C5="","",SUM(E5,F5,46.7)/28.7)

so you can copy it down to the bottom of the column. It populates only when a time is entered in C. Otherwise it returns 1.6.

Biochemically, you cannot convert spot glucose measurements to HbA1c... they are measuring entirely different things. Blood glucose reflects the situation NOW, whereas HbA1c is an approximation of the glucose load in your red blood cells over the life of an erythrocyte, which is ~120 days. If you really want to do something like that... you would need to be measuring the AREA under the blood glucose:time curve, not an average of individual measurements.

That could be another Excel problem for another day!