simple currency addition in cell without having to enter decimal

[John]

I want to add dollar amounts in an individual cell with a fixed decimal
point. In other words, click on a single cell, press =, then enter multiple
numbers (preceeded or followed by the + or - sign) without having to enter
decimal for each amount entered.

I have worn myself out trying to format a cell to perform this way but
haven't conquered it yet. Even though it works fine when entering a single
number in a cell, it doesn't work when entering multiple numbers this way in
a single cell. As soon as I press = in the cell, all numbers display without
a fixed decimal point. I have to enter it manually and do not want to have
to do so.

Thanks in Advance,
John Couch

 

[Bernard Liengme]

Use Tools | Options; open Edit tab; check the Fixed Decimal box and make
sure 2 shows in the window.
Now type numbers such as:
12345 -> this becomes 123.45 when you press Enter, tab, or otherwise commit
the entry
-12345 -> this becomes -123.45 .....
best wishes

 

[John]

Hi Ben,

Thanks for taking time to respond.

I have done this. Try the example in my message. Click on a cell...press
=... then enter 21536+63654+34845 (wanting values to be shown and computed as
215.36 +636.54+ 348.45). Note the figures do not have fixed decimal when you
enter. I want them to without having to manually enter the decimal point.

Give it a try and tell me what I am doing wrong, please.

 

[Ron Coderre]

I understand what you want....but, let's talk about what Excel can do
(without custom programming), OK?

Try this:

Select the input cells
Then...from the Excel main menu:
<format><cells><number tab>
Category: Custom
Type: 0\.00
Click the [OK] button

What that will do is display any number with a "decimal point" 2 places to
the left of the actual decimal point.

Examples:
A1: 100...........displays as 1.00
A2: 101...........displays as 1.01
A3: =100+201+305........displays as 6.06

Just keep in mind that the displayed amount is NOT the actual amount. Other
cells that refer to the custom formatted cells will need to divide those
cells by 100 to convert them to the displayed value.

I hope that makes sense.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[John]

Ron,

YOU ARE THE CONSUMATE ADVISOR and I cannot thank you enough for your
guidance! The "totals" in the individual cells now work explicitly how I
would like for them to as long as I format each of them per your direction.
It would be nice if the actual number, entered without the decimal point,
displayed as such but, this is what I need for the cell's results to be.
Maybe you can give MS a nudge to see that this happens in an upgrade soon.

Thank you so much in solving a problem that has baffled me for months! You
are correct in that the display while entering the cell's various additive
values does not display the auto decimal but displays the results correctly
when "Enter" is pressed.

I can't thank you enough. Have a great evening and thanks for being on
board to help us get the full power from Excel!

Warmest regards,
John Couch

I understand what you want....but, let's talk about what Excel can do
(without custom programming), OK?

Try this:

Select the input cells
Then...from the Excel main menu:
<format><cells><number tab>
Category: Custom
Type: 0\.00
Click the [OK] button

What that will do is display any number with a "decimal point" 2 places to
the left of the actual decimal point.

Examples:
A1: 100...........displays as 1.00
A2: 101...........displays as 1.01
A3: =100+201+305........displays as 6.06

Just keep in mind that the displayed amount is NOT the actual amount. Other
cells that refer to the custom formatted cells will need to divide those
cells by 100 to convert them to the displayed value.

I hope that makes sense.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

I want to add dollar amounts in an individual cell with a fixed decimal
point. In other words, click on a single cell, press =, then enter multiple
numbers (preceeded or followed by the + or - sign) without having to enter
decimal for each amount entered.

I have worn myself out trying to format a cell to perform this way but
haven't conquered it yet. Even though it works fine when entering a single
number in a cell, it doesn't work when entering multiple numbers this way in
a single cell. As soon as I press = in the cell, all numbers display without
a fixed decimal point. I have to enter it manually and do not want to have
to do so.

Thanks in Advance,
John Couch

 

[John]

Hi Ron,

I did the routine for the individual cells H7 & H8. But, when totaling
those cells, the sum is not formatted as is the individual cells. This
inaccuracy will occur again in cells H11 & H12. It also misreports H19 &
H20. If you have done Sales & Use Tax before, you’ll understand the goal of
this spreadsheet.



Attached is the spreadsheet so you can actually see what I’m trying to do.
Again, I appreciate your support.

Ron,

YOU ARE THE CONSUMATE ADVISOR and I cannot thank you enough for your
guidance! The "totals" in the individual cells now work explicitly how I
would like for them to as long as I format each of them per your direction.
It would be nice if the actual number, entered without the decimal point,
displayed as such but, this is what I need for the cell's results to be.
Maybe you can give MS a nudge to see that this happens in an upgrade soon.

Thank you so much in solving a problem that has baffled me for months! You
are correct in that the display while entering the cell's various additive
values does not display the auto decimal but displays the results correctly
when "Enter" is pressed.

I can't thank you enough. Have a great evening and thanks for being on
board to help us get the full power from Excel!

Warmest regards,
John Couch

I understand what you want....but, let's talk about what Excel can do
(without custom programming), OK?

Try this:

Select the input cells
Then...from the Excel main menu:
<format><cells><number tab>
Category: Custom
Type: 0\.00
Click the [OK] button

What that will do is display any number with a "decimal point" 2 places to
the left of the actual decimal point.

Examples:
A1: 100...........displays as 1.00
A2: 101...........displays as 1.01
A3: =100+201+305........displays as 6.06

Just keep in mind that the displayed amount is NOT the actual amount. Other
cells that refer to the custom formatted cells will need to divide those
cells by 100 to convert them to the displayed value.

I hope that makes sense.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

I want to add dollar amounts in an individual cell with a fixed decimal
point. In other words, click on a single cell, press =, then enter multiple
numbers (preceeded or followed by the + or - sign) without having to enter
decimal for each amount entered.

I have worn myself out trying to format a cell to perform this way but
haven't conquered it yet. Even though it works fine when entering a single
number in a cell, it doesn't work when entering multiple numbers this way in
a single cell. As soon as I press = in the cell, all numbers display without
a fixed decimal point. I have to enter it manually and do not want to have
to do so.

Thanks in Advance,
John Couch

 

[Ron Coderre]

Hi, John

First..a cite from my prior post:cells that refer to the custom formatted cells will need to divide those
cells by 100 to convert them to the displayed value.<<

So the formula in H9 would be: =(H7-H8)/100

Next...a suggestion:
Bit the bullet and use the decimal point! Especially easiy if you are using
a standard keyboard with the numeric keypad on the right side. The "solution"
I provided is really just a weak workaround. Whatever you're saving in
keystrokes you're losing in extra formula composition. If the numbers in the
formulas are entered with their respective decimal points, you never have to
question whether a cell display has been compromised to so save you a
nanosecond or two in input.

I hope that helps
***********
Regards,
Ron

XL2002, WinXP

Hi Ron,

I did the routine for the individual cells H7 & H8. But, when totaling
those cells, the sum is not formatted as is the individual cells. This
inaccuracy will occur again in cells H11 & H12. It also misreports H19 &
H20. If you have done Sales & Use Tax before, you’ll understand the goal of
this spreadsheet.



Attached is the spreadsheet so you can actually see what I’m trying to do.
Again, I appreciate your support.

Ron,

YOU ARE THE CONSUMATE ADVISOR and I cannot thank you enough for your
guidance! The "totals" in the individual cells now work explicitly how I
would like for them to as long as I format each of them per your direction.
It would be nice if the actual number, entered without the decimal point,
displayed as such but, this is what I need for the cell's results to be.
Maybe you can give MS a nudge to see that this happens in an upgrade soon.

Thank you so much in solving a problem that has baffled me for months! You
are correct in that the display while entering the cell's various additive
values does not display the auto decimal but displays the results correctly
when "Enter" is pressed.

I can't thank you enough. Have a great evening and thanks for being on
board to help us get the full power from Excel!

Warmest regards,
John Couch

I understand what you want....but, let's talk about what Excel can do
(without custom programming), OK?

Try this:

Select the input cells
Then...from the Excel main menu:
<format><cells><number tab>
Category: Custom
Type: 0\.00
Click the [OK] button

What that will do is display any number with a "decimal point" 2 places to
the left of the actual decimal point.

Examples:
A1: 100...........displays as 1.00
A2: 101...........displays as 1.01
A3: =100+201+305........displays as 6.06

Just keep in mind that the displayed amount is NOT the actual amount. Other
cells that refer to the custom formatted cells will need to divide those
cells by 100 to convert them to the displayed value.

I hope that makes sense.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


:

I want to add dollar amounts in an individual cell with a fixed decimal
point. In other words, click on a single cell, press =, then enter multiple
numbers (preceeded or followed by the + or - sign) without having to enter
decimal for each amount entered.

I have worn myself out trying to format a cell to perform this way but
haven't conquered it yet. Even though it works fine when entering a single
number in a cell, it doesn't work when entering multiple numbers this way in
a single cell. As soon as I press = in the cell, all numbers display without
a fixed decimal point. I have to enter it manually and do not want to have
to do so.

Thanks in Advance,
John Couch

 

[John]

Thanks, Ron,

I felt like I was asking the program for a little too much. I use
Quickbooks and never have to enter a decimal point so when I switch back and
forth between programs it gets a little inconvenient.

FYI, I have been using a left handed keyboard with the numeric pad on the
left side for the past 10 years which allows me to keep my right hand on the
mouse at all times. It is so efficient that I can't believe it's not the
industry standard! I also run a lot of AutoCAD and really helps in "power
use" and accuracy.

Thanks again for your expertise!

John

Hi, John

First..a cite from my prior post:cells that refer to the custom formatted cells will need to divide those
cells by 100 to convert them to the displayed value.<<

So the formula in H9 would be: =(H7-H8)/100

Next...a suggestion:
Bit the bullet and use the decimal point! Especially easiy if you are using
a standard keyboard with the numeric keypad on the right side. The "solution"
I provided is really just a weak workaround. Whatever you're saving in
keystrokes you're losing in extra formula composition. If the numbers in the
formulas are entered with their respective decimal points, you never have to
question whether a cell display has been compromised to so save you a
nanosecond or two in input.

I hope that helps
***********
Regards,
Ron

XL2002, WinXP

Hi Ron,

I did the routine for the individual cells H7 & H8. But, when totaling
those cells, the sum is not formatted as is the individual cells. This
inaccuracy will occur again in cells H11 & H12. It also misreports H19 &
H20. If you have done Sales & Use Tax before, you’ll understand the goal of
this spreadsheet.



Attached is the spreadsheet so you can actually see what I’m trying to do.
Again, I appreciate your support.

Ron,

YOU ARE THE CONSUMATE ADVISOR and I cannot thank you enough for your
guidance! The "totals" in the individual cells now work explicitly how I
would like for them to as long as I format each of them per your direction.
It would be nice if the actual number, entered without the decimal point,
displayed as such but, this is what I need for the cell's results to be.
Maybe you can give MS a nudge to see that this happens in an upgrade soon.

Thank you so much in solving a problem that has baffled me for months! You
are correct in that the display while entering the cell's various additive
values does not display the auto decimal but displays the results correctly
when "Enter" is pressed.

I can't thank you enough. Have a great evening and thanks for being on
board to help us get the full power from Excel!

Warmest regards,
John Couch


:

I understand what you want....but, let's talk about what Excel can do
(without custom programming), OK?

Try this:

Select the input cells
Then...from the Excel main menu:
<format><cells><number tab>
Category: Custom
Type: 0\.00
Click the [OK] button

What that will do is display any number with a "decimal point" 2 places to
the left of the actual decimal point.

Examples:
A1: 100...........displays as 1.00
A2: 101...........displays as 1.01
A3: =100+201+305........displays as 6.06

Just keep in mind that the displayed amount is NOT the actual amount. Other
cells that refer to the custom formatted cells will need to divide those
cells by 100 to convert them to the displayed value.

I hope that makes sense.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


:

I want to add dollar amounts in an individual cell with a fixed decimal
point. In other words, click on a single cell, press =, then enter multiple
numbers (preceeded or followed by the + or - sign) without having to enter
decimal for each amount entered.

I have worn myself out trying to format a cell to perform this way but
haven't conquered it yet. Even though it works fine when entering a single
number in a cell, it doesn't work when entering multiple numbers this way in
a single cell. As soon as I press = in the cell, all numbers display without
a fixed decimal point. I have to enter it manually and do not want to have
to do so.

Thanks in Advance,
John Couch

 

[Ron Coderre]

Keyboard with the numeric keypad on the left!
Great idea....Too bad there were so many QWERTY's already in place by the
time the mouse was invented. (maybe it's not too late for me to convert)


***********
Regards,
Ron

XL2002, WinXP

Thanks, Ron,

I felt like I was asking the program for a little too much. I use
Quickbooks and never have to enter a decimal point so when I switch back and
forth between programs it gets a little inconvenient.

FYI, I have been using a left handed keyboard with the numeric pad on the
left side for the past 10 years which allows me to keep my right hand on the
mouse at all times. It is so efficient that I can't believe it's not the
industry standard! I also run a lot of AutoCAD and really helps in "power
use" and accuracy.

Thanks again for your expertise!

John

Hi, John

First..a cite from my prior post:

Just keep in mind that the displayed amount is NOT the actual amount. Other

cells that refer to the custom formatted cells will need to divide those
cells by 100 to convert them to the displayed value.<<

So the formula in H9 would be: =(H7-H8)/100

Next...a suggestion:
Bit the bullet and use the decimal point! Especially easiy if you are using
a standard keyboard with the numeric keypad on the right side. The "solution"
I provided is really just a weak workaround. Whatever you're saving in
keystrokes you're losing in extra formula composition. If the numbers in the
formulas are entered with their respective decimal points, you never have to
question whether a cell display has been compromised to so save you a
nanosecond or two in input.

I hope that helps
***********
Regards,
Ron

XL2002, WinXP

Hi Ron,

I did the routine for the individual cells H7 & H8. But, when totaling
those cells, the sum is not formatted as is the individual cells. This
inaccuracy will occur again in cells H11 & H12. It also misreports H19 &
H20. If you have done Sales & Use Tax before, you’ll understand the goal of
this spreadsheet.



Attached is the spreadsheet so you can actually see what I’m trying to do.
Again, I appreciate your support.



:

Ron,

YOU ARE THE CONSUMATE ADVISOR and I cannot thank you enough for your
guidance! The "totals" in the individual cells now work explicitly how I
would like for them to as long as I format each of them per your direction.
It would be nice if the actual number, entered without the decimal point,
displayed as such but, this is what I need for the cell's results to be.
Maybe you can give MS a nudge to see that this happens in an upgrade soon.

Thank you so much in solving a problem that has baffled me for months! You
are correct in that the display while entering the cell's various additive
values does not display the auto decimal but displays the results correctly
when "Enter" is pressed.

I can't thank you enough. Have a great evening and thanks for being on
board to help us get the full power from Excel!

Warmest regards,
John Couch


:

I understand what you want....but, let's talk about what Excel can do
(without custom programming), OK?

Try this:

Select the input cells
Then...from the Excel main menu:
<format><cells><number tab>
Category: Custom
Type: 0\.00
Click the [OK] button

What that will do is display any number with a "decimal point" 2 places to
the left of the actual decimal point.

Examples:
A1: 100...........displays as 1.00
A2: 101...........displays as 1.01
A3: =100+201+305........displays as 6.06

Just keep in mind that the displayed amount is NOT the actual amount. Other
cells that refer to the custom formatted cells will need to divide those
cells by 100 to convert them to the displayed value.

I hope that makes sense.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


:

I want to add dollar amounts in an individual cell with a fixed decimal
point. In other words, click on a single cell, press =, then enter multiple
numbers (preceeded or followed by the + or - sign) without having to enter
decimal for each amount entered.

I have worn myself out trying to format a cell to perform this way but
haven't conquered it yet. Even though it works fine when entering a single
number in a cell, it doesn't work when entering multiple numbers this way in
a single cell. As soon as I press = in the cell, all numbers display without
a fixed decimal point. I have to enter it manually and do not want to have
to do so.

Thanks in Advance,
John Couch