how can I set up engineering notation in numbers '09?

My solution will be like this. ( checked on Numbers 3.6.2 )

B := LOG10( ABS( A ) )

just to simplify the formula for C

C := IF( B ≥ 0, FLOOR( B , 3 ), CEILING( B, −3 ) )

with special cell format for leading 0 and plus sign

D := FIXED( A ÷ (10^C) , 2 ) & "E" & C

0

I also tested LibreOffice 5.1.5.2 ( latest stable ), which supports Excel like cell format option ( ##0.00E+00 ), however the format is not transferred to Numbers even if the spreadsheet is saved as Excel's.

Good Morning all,

1:30 am and it is past my bedtime. Playing with this problem, I thought that the Functions LOOKUP() RIGHT() and MOD() could be applied to a formula in the Table on the right to find exponents (times 3) from the Table on the left.

Goodnight,

Ian.

At the risk of talking to ourselves here, since maineiac70 hasn't responded since the OP, here is a possible solution, or at least a starting point.

Of course, the interposing columns B-D would be Hidden. The formula in E is:

=IF(C=2, LEFT(D, 4)/10&"E"&B+1, IF(C=1, LEFT(D, 3)/100&"E"&B+2, LEFT(D, 4)&"E"&B))

Regards,

Jerry

Good Evening All,

eBomber's solution works if the OP has Excel (but as Jerry noted, the OP has not responded, so we are talking amongst ourselves).

Jerry's solution, using LEFT() works.

I found this works, using RIGHT() to grab the exponents of the straight Scientific value and Scientific divided by 10 and 100. (No need to consider the + or - of the exponent at this stage). Then find MOD(<each exponent>, 3) and test with IF() to see which MOD is zero (i.e. which exponent is an exact multiple of 3). Then grab the relevant ("Engineering") value from Columns B, C or D.

In Cell E2 and Filled Down:

=IF(MOD(RIGHT(B2,2),3)=0,B2,IF(MOD(RIGHT(C2,2),3)=0,C2,IF(MOD(RIGHT(D2,2),3)=0,D 2,"")))

Hide Columns B, C and D for a neat look.

Regards,

Ian.

Hi maineiac,

I understand. You want multiples of 1000 (micro, milli, Kilo, Mega...) for SI units.

eBombers solution from the link https://discussions.apple.com/thread/4820887?answerId=22307816022#22307816022

Hi, there is a small trick to engineering notation in Numbers:

You need a excel spreadsheet that has a cell in engineering notation.

Open it with numbers, copy the style and paste it into the cells of your spreadsheet and there you have! :O

Thank you for the link, eBomber. Is it possible to paste an example of that Excel format here? Could that be used to create a Custom Format in Numbers?

Regards,

Ian.

Hi,

Numbers doesn't have any built in format to do this, but it should be possible to set up a conversion from scientific notation to engineering notation, with a text string as the output. The result would be 'read only,' not useable in further calculations, but the number itself (in the cell formatted for scientific notation) would still exist and be useable.

What range of magnitudes are we talking about? What precision do you want in the results? A sample list showing the extremes (in scientific notation) would likely be useful.

Regards,

Barry

eBomber wrote:

In excel, with that format you get scientific notation, but in which the exponent is a multiple of three, which is what is intended (I think)

Yes, multiples of three in the exponent is what Engineering Notation is. The question is whether you could get by with scientific notation. In my past experience, Excel, like Numbers, has Scientific Notation but not Engineering Notation, despite eBomber's claim which I have to way to confirm. Perhaps he could post a screen shot of an Excel example showing the Format tool. All other references I've seen deny the presence in Excel. I just checked LibreOffice and it doesn't seem to be there either, and LibreOffice is usually a good mirror of Excel.

Barry has made the point that if it is critical to you, you can create a text string to simulate Engineering Notation. It's not going to be simple though. He may be working on an expression to do this in Numbers right now. 🙂

The easiest way I can think of would be to just type the answer in an Engineering Notation equivalent of the calculated Scientific Notation result and hide the calculation.

Jerry

eBomber,

I'm trying to understand how you did that custom format so that it would only use even threes for exponents so I can duplicate it in LibreOffice. I think I see how you created the basic format, but not how you specified the threes.

Jerry

Jerry in OpenOffice does not work (I guess in LibreOffice either, as it is more or less the same), but in Excel it works, I use it frequently.

The trick as I said is open in Numbers an Excel spreadsheet that has a cell with the engineering format. From there you can copy the style and use it in Numbers spreadsheets 😕

To add to the talking to oneself:

The space between the D and 2 for the last D2 in the equation will cause an error. Remove it and the formula should work.

Setup the calculations similar in the other cells and you should get the engineering format.

select the cell(s) you want to format as Engineering Notation, then open the Cell Inspector and select "Scientific":

Two captures from Excel:

and the same file open with Numbers:

and without use formulas.

Maybe this help:

https://discussions.apple.com/thread/4820887?answerId=22307816022#22307816022

It is this:

##0.0E+00

but I see no way to create it in Numbers 😟

eB,

Do you mind if the result is Scientific Notation rather than Engineering Notation?

Your last post's example could be either.

Jerry