Google Sheets: Formatting a cell with 0.00000000

[Jetheat]

I'm using Google Sheets.

I have managed to format a cell so that it has 8 numbers after the decimal point like 0.00000000.

Now what I wanna do is enter a number like 327 and have it appear at the end of those trailing zeros rather than appear before the decimal point.
So I want it to say 0.00000327 instead of 327.00000000.

Any idea how I can do that?

JH

[BMV]

there are
1 - 8 cf
2 Number CF
3 3 cf with Number CF

[Jetheat]

That's great. Thank you.
I'm not very good with Excel. Can you tell me what formula I need to type into the cells to format them so they work the way I need?

JH

[BMV]

Jetheat i am sorry but it don't work with Goggle sheets

[MrShorty]

My inclination would be to simply enter 327e-8.

As near as I can tell, Google Sheets does not have a built in way to accomplish this. If you don't want to deal with this at data entry, the next easiest approach, IMO, is to enter 327 into one cell. In an adjacent cell, enter the formula =A1*1E-8 (obviously, replace A1 with the actual cell reference where you entered the 327). You end up with one column for data entry and a second column to hold the real value, but Google sheets should have enough spreadsheet real estate to handle the extra column.

I would handle this with one of those strategies.

[Jetheat]

This may not work coz I will be entering different numbers across the sheet without referencing any particular hard wired cell.

[MrShorty]

Which of my solutions may not work? I can see that the second solution may not work, but I cannot imagine that Google sheets is incapable of receiving 327e-8 as a valid input value.

A quick browse of Google sheet's menus and options does not find a "automatically insert a decimal point" option like Excel has (and Excel's option is not always a good option to use because it is a global option). If Google sheets has a built in option for this, then it is well hidden.

Just as Excel uses VBA as a macro language, I know that Google Sheets can use Javascript as a macro language. I expect that one who was familiar with Javascript and how to integrate it with Google Sheets could write a macro that would do this -- and maybe even tie it to a Google Sheets "event" so that it will occur automatically when a value is entered. I have no experience with Javascript or using it in Google Sheets, so I have no idea what that would look like (and I have gotten the impression in the past that relatively few if any of us on this forum have much experience with Javascript in Google Sheets).

[Jetheat]

Instead of typing e-8 into every cell after the number, is there a way to format it so that's automatically included?

JH

[MrShorty]

As I said, I cannot find anything equivalent in the menus. If Google sheets has an "auto scale upon entry" type of feature, it is well hidden. If they don't have such a feature, then it would be easy (for someone who understands how) to create a macro to do it, but I certainly don't, and I'm not sure anyone here is comfortable with writing macros for Google Sheets.

I'm sympathetic to what you are trying to do -- reduce the number of keystrokes you need to make at data entry. At present, I see three options:
1) Bite the bullet and enter the extra characters/digits (.00000328 or 328e-8).
2) Use two cells -- one for data entry and one with a formula that multiplies by 1e-8.
3) Develop and use a macro.

At this point, I would say 1 and 2 are the easiest to implement, but you have rejected both of those. At this point, I would perform a kind of "cost/benefit" analysis and decide if I thought I would ever recoup the effort of developing a macro to do what options 1 or 2 do fairly easily.

[Jetheat]

2) Use two cells -- one for data entry and one with a formula that multiplies by 1e-8.

With your second option, it seems like I would need to write the 3 digits in another cell first.
But the whole issue is that I want to write any digit in a particular column and have it automatically revert to being at the end of 8 decimal places.

So instead of writing the actual number in a different cell, is there a way to do it within the same cell by formatting it with 1e-8 behind the scenes?

So whether I type 123 or 654321, it will automatically revert to 0.00xxxxxx after pressing enter.

JH

[MrShorty]

With your second option, it seems like I would need to write the 3 digits in another cell first.

that is correct.

is there a way to do it within the same cell by formatting it with 1e-8 behind the scenes?

Not that I have been able to find in Google Sheets (at least, not without a macro).

I guess another option that I have kind of ignored (because it doesn't change the entry value) -- could you edit each formula that refers to these entered cells to handle the multiplication? Substitute reference with reference*1e-8 in the formula (where reference is the data entry cell). This won't change the value in the entered cell (if you entered 123 in reference, it will still be 123), but each formula that uses the reference can be edited to account for this.

[Jetheat]

I don't understand.

Can you give me a formula example?

JH

[MrShorty]

As a simple example of my latest suggestion, let's say that my goal is compute the square root of 328E-8. I can:

1) Enter 328e-8 into A1, and enter the formula =SQRT(A1) into B1.
2) I can enter 328 into A1, and enter the formula =SQRT(A1*1e-8).

The result in B1 is the same, but I saved some data entry keystrokes in A1.