4

I want to make a spreadsheet with Google Sheets to compare microprocessor's specifications. One column will show processor speed, another will show program space, another for RAM space and so on.

What I'd like is a custom number format that could display values to something along these lines:

Processor speed: I enter into a cell:

  • "1,000,000,000" which then displays as "1GHz"
  • "20,000,000" which then displays as "20Mhz"
  • "16,500,000" which then displays as "16.5MHz"

EEPROM: I enter into a cell:

  • "12,000" which then displays as "12kB
  • "765" which then displays as "765B"

How can I do this? I know some javascript programming, so perhaps I could do it that way. Perhaps there's a built in way of doing it though? I can't even find javascript API which lets me create custom number formatting rules other than the syntax characters that can be used to create a custom number format, as per this help document.

I will have one row for each microprocessor, and one column for a specification (e.g. one column for processor speed, one column for 32 bit/ 64 bit). I will want to sort them, so 20,000 should be sorted as being greater than 20, even though 20GHz sorted alphabetically would be less than 20Mhz. I will want to be able to sort the columns

Edit:

To clarify, I want the formatted version (e.g. 1GHz) to appear in the same cell as the one I enter the number 1,000,000,000.

For example, All of column AB I use for processor speed. In any cell of column AB I enter a number, it will format it into GHz/MHz/kHz/Hz.

In other words, say I use column AE for the price of the microprocessor. To set up the formatting, I highlight the whole column "AE", then in the menu I go to "Format" > "Number" > "Currency". Then when I enter 3.45 into any cell in column "AE", the number re-formats to £3.45.

2

Look into the following functions: IF and CONCATENATE.

Taking the frequency as an example: you have one cell, B23 in this example, that contains your "raw" number. 2.2 GHz would be entered as 2200000000. Your sheet will be sortable by this column.

Then in C23, put this formula:

=if(B23>=1000000000,B23/1000000000,if(B23>=1000000,B23/1000000,if(B23>=1000,B23/1000,B23)))

In D23, this formula:

=if(B23>=1000000000,"GHz",if(B23>=1000000,"MHz",if(B23>=1000,"kHz","Hz")))

And in E23, this formula, which produces your formatted value:

=concatenate(C23," ",D23)

You don't actually need three cells. You can have just one cell, putting the IF functions in the parameters of the CONCATENATE function:

=concatenate(if(B23>=1000000000,B23/1000000000,if(B23>=1000000,B23/1000000,if(B23>=1000,B23/1000,B23)))," ", if(B23>=1000000000,"GHz",if(B23>=1000000,"MHz",if(B23>=1000,"kHz","Hz"))))

Using three cells just makes it easier to keep things straight, especially the first few times you use nested functions.

For values using binary prefixes (traditionally used for memory sizes), the factors change to 1024, 1024 squared, etc. Note that the prefix for 1000 is lowercase "k" while for 1024 it is uppercase "K"; M and G are always uppercase.

  • Great - thanks, + 1, but your solution needs two cells - one for entering the raw number, and a second one that displays the formatted version using your formula. I was hoping for something which applies the formatting to the same cell that I enter the numbers. Please see my edited question – CL22 Jun 15 '15 at 9:45
  • @Jodes The way you want is impossible to achieve. You could have a script replacing the entered number with text like "11.5GHz", but it will be purely text: so, no calculations and no sorting by magnitude (can only sort alphabetically). For currencies and time, you can have a formatted value that can still be used as a number -- but that's only because the support for these formats is built into the software. For other quantities, it isn't. – user79865 Jun 15 '15 at 20:57
  • @user79865 I don't think it is impossible. You can change formatting options with script according to value, hooking to onEdit(). See my answer. – ESL May 21 '18 at 4:11
1

The only way I think you could do that is with Google Script. I'm not an expert, I try last night but it takes many steps (and currently I don't have time to browse the references).

But, in short steps, I think that you should:

  1. Make a named range with the cells that you want to format in that way.
  2. Write a function like checkFreqs() and call it into the function onEdit() (this one is called every time the user made an edition).
  3. Get the named range with something like: var fRange = e.source.getRangeByName(FREQ_RANGE_NAME); where FREQ_RANGE_NAME is the name of the named range. If there is no cells, or no named range, return.
  4. Make a light check to corroborate if e.range intersects fRange (compare using .getLastColumn(), .getLastColumn(), .getRow() and .getLastRow of both ranges, ideally with a proper function). If there is no intersection, return.
  5. For every cell in fRange, compare the magnitude order of the value, and adjust the format. I think it is something like:

    function formatFreq(cell)
    {
      var order = Math.floor(Math.log10(value)/3)*3;
      var unit = 'Hz';
      switch(order)
      {
        case 3: unit='k'+unit; break;
        case 6: unit='M'+unit; break;
        case 9: unit='G'+unit; break;
        case 12: unit='T'+unit; break;
        case 15: unit='P'+unit; break;
      }
      var format = '0.00 "'+unit+'"';
      cell.setNumberFormat([format,format,format]);
    }

I hope this will guide you enough!

1

This is totally working for byte formatting (bytes - GB):

=if(B23>=1024; if(B23>=(1024*1024); if(B23>=(1024*1024*1024); B23/(1024*1024*1024)&" GB"; B23/(1024*1024)&" MB");B23/1024&" kB");B23&"bytes")

Can be used for any other magnitude by just altering the numbers and the string labels.

Note: you need an & between the number and the following string label in google sheets as well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.