Dear God - someone HELP!!!

[vivo4]

I'm nearly going out of my mind trying to format a Mileage
Log in Excel 2002. My needs are specific and don't seem
to fit any of the ready-made templates out there. I need
the day-numbers in a month (1-31) down the left-hand
column. Excel (being written for nuclear physicists who
don't appear to bother with such things) won't allow me
just to enter numbers. Every number I type in those cells
comes out as 0. I have tried formatting the cells
to "Number" with no decimals, to no avail. (I am NOT
going to write in every number in this Log as "1.0"
for "1", "2.0" for "2" etc. which seems to be the only way
it knows what I want.) I have tried formatting them
as "Date" but I don't want specific day detail. I just
want it to list 1-31 sequentially down the left side.
Lastly, I would really like to know how to set up a
formula in certain columns which multiplies the data in
the corresponding cell in the previous column (kms.
driven) by 0.39 (reimbursement per km.) to arrive at a
cost per trip amount. I have been able to format one cell
at a time to do that, but can't seem to come up with a
formula which covers a range of cells. The help section
of Excel is useless, btw, in case anyone from MS reads
this.
Mark

 

[Jim Rech]

Sounds as if you might have Fixed Decimal selected under Tools, Options,
Edit.

 

[Mark Graesser]

Mark,
I think the first problem you have, the zero thing, is probably becuase the fixed decimal place got turned on. If you type in 1, do you see .0001 in the formula bar at the top of the sheet? Go to Tools>Options>Edit from the pulldown menus. If "Fixed Decimal" is checked then uncheck it.

Also, a quick way to get a series of numbers is to type in the first two cells in the range, then select both cells, grab the fill handle (little black square in lower right corner of selection) with your left mouse button, and roll down to where you want to end.



For your formula, say the milage is in B2. The in C2 enter:

=B2*0.39

You can then fill this formula down to the end of your sheet. This can also be done by selecting only that cell and dragging the fill handle.

If you don't want to see all of the zeroes, you can do two things. The first is Tools>Options>View and uncheck Zero Values at the bottom of the dialog box. However this will hide all zero values on the sheet. If you only want to hide the zeros for this formula then you can use this formula instead of the one above:

=IF(B2=0,"",B2*0.39)

Now if B2 equals zero the formula will return a blank.



Lastley, you can select multiple cells at a time, and format them all together.

Post back on this thread if you have any problems.

Good Luck,
Mark Graesser
(e-mail address removed)

----- vivo4 wrote: -----

I'm nearly going out of my mind trying to format a Mileage
Log in Excel 2002. My needs are specific and don't seem
to fit any of the ready-made templates out there. I need
the day-numbers in a month (1-31) down the left-hand
column. Excel (being written for nuclear physicists who
don't appear to bother with such things) won't allow me
just to enter numbers. Every number I type in those cells
comes out as 0. I have tried formatting the cells
to "Number" with no decimals, to no avail. (I am NOT
going to write in every number in this Log as "1.0"
for "1", "2.0" for "2" etc. which seems to be the only way
it knows what I want.) I have tried formatting them
as "Date" but I don't want specific day detail. I just
want it to list 1-31 sequentially down the left side.
Lastly, I would really like to know how to set up a
formula in certain columns which multiplies the data in
the corresponding cell in the previous column (kms.
driven) by 0.39 (reimbursement per km.) to arrive at a
cost per trip amount. I have been able to format one cell
at a time to do that, but can't seem to come up with a
formula which covers a range of cells. The help section
of Excel is useless, btw, in case anyone from MS reads
this.
Mark

 

[Andy Brown]

Are you the same vivo from TSG? Maybe you didn't see my reply + screenshot
when the server went down.

Would you like me to send the demo file privately, then you can tell me how
you want it tweaked?

Rgds,
Andy

 

[Lady Layla]

Hi Vivo

Not really sure what your problem is as I do this on a daily basis.

Regarding the day -- make sure that Tools Options Edit Fixed decimals is not
checked


If you run the days down column A and have the mileage in Column B

Say A 3 = 1 B3 = 35.2 then in C3 enter =B3*0.39. Then copy C3 and paste
from D3 down to the row with your last date (34 I think)

That will give you the mileage reimbursement per day


(Actually the Help section in Excel is very good)


: I'm nearly going out of my mind trying to format a Mileage
: Log in Excel 2002. My needs are specific and don't seem
: to fit any of the ready-made templates out there. I need
: the day-numbers in a month (1-31) down the left-hand
: column. Excel (being written for nuclear physicists who
: don't appear to bother with such things) won't allow me
: just to enter numbers. Every number I type in those cells
: comes out as 0. I have tried formatting the cells
: to "Number" with no decimals, to no avail. (I am NOT
: going to write in every number in this Log as "1.0"
: for "1", "2.0" for "2" etc. which seems to be the only way
: it knows what I want.) I have tried formatting them
: as "Date" but I don't want specific day detail. I just
: want it to list 1-31 sequentially down the left side.
: Lastly, I would really like to know how to set up a
: formula in certain columns which multiplies the data in
: the corresponding cell in the previous column (kms.
: driven) by 0.39 (reimbursement per km.) to arrive at a
: cost per trip amount. I have been able to format one cell
: at a time to do that, but can't seem to come up with a
: formula which covers a range of cells. The help section
: of Excel is useless, btw, in case anyone from MS reads
: this.
: Mark

 

[vivo4]

Thanks for your suggestions all those of you who suggested
that I uncheck the Fixed Decimal box under Tools, Edit,
whatever.... It worked! Now to get on with all the rest
of my formatting requirements. Andy Brown is giving me a
hand on that by email and it's coming along (just a few
more formulas to go!
Mark

 

[Andy Brown]

Oh dear Lord, you poor soul! He "helped" me once, now I'm on a bottle of
Jack a day!!

Just kidding, we're mostly done, thanks all, etc., etc.

Rgds,
Andy