Ridiculously easy excel formula that is beyond me

WeegyAVLover

Distinguished Member
Joined
May 3, 2007
Messages
6,085
Reaction score
2,988
Points
1,872
Location
Scotland
Righty then...

Excel doooberywotsiters....

I have an excel formula that is easy I am sure but I cannot figure it out.

I have a number of fields with (example A1 to A5) with various sizes in GB listed, I want to:

- add these up.
- divide by 1024 to put in TBs
- add TB text to end of formula.

So once formula runs, the output should display - 1TB.

Then formula I can get working is:

=sum(a1:a5)& " TB")

However this only gives me the GB read out with the TB initials at the end.

when I add the divide in:

=sum(a1:a5)/1024 & " TB"

I get an error.
I have tried a number of different combinations but none seem to be working.
Any suggestions as I am sure this is a simple one and I am just being thick.

Col
 
Seems to work for me:

Excel.png
 
Have you tried:

=(sum(a1:a5))/1024 & " TB"
 
you can use custom cell formats to just put a "TB" at the ned of a number rather than adding it as text through the formula bar - also helps by keeping the cell as a pure number that can then be used for other things more easily.....

So keep the formula purely the adding / dividing maths bit, then go to format cell, number, custom and use something like

#,##0 "TB"

as the custom format.......
 
It will likely ignore the format on your & " GB" cells because they are string data types rather numeric - andyparksy's solution should go some way to sorting that.
 
Using a custom format is the way to go - then you can still treat them as numbers and sum/add/divide etc between them. If you put &" TB" on the end of your number then it just becomes a string and you can't do significant figure/decimal places formatting, comma thousands grouping etc.
 
...
when I add the divide in:

=sum(a1:a5)/1024 & " TB"
..
One technique which works for me when I get this kind of issue (we all do sometimes) is to tackle it piecemeal:

Enter the '=sum(a1:a5)', and see if that works;
then edit the entry by adding the '/1024' and check that
Finally add the '"& TB"'.

Either it finally works or it doesn't. If it doesn't, you know where in the formula the error lies. Don't forget to edit the previous entry every time, not retype it.
 
you can use custom cell formats to just put a "TB" at the ned of a number rather than adding it as text through the formula bar - also helps by keeping the cell as a pure number that can then be used for other things more easily.....

So keep the formula purely the adding / dividing maths bit, then go to format cell, number, custom and use something like

#,##0 "TB"

as the custom format.......

Thanks Andy, I missed your comment and it has worked a dream, definitely the easiest way to go.

It will likely ignore the format on your & " GB" cells because they are string data types rather numeric - andyparksy's solution should it.go some way to sorting that.

Thanks for pointing out Andy's post, I had missed it originally.
 
Aside from the actual excel formula, are you aware that the industry commonly does not use 1024 and the multiplier between KB, MB, GB and TB as they should do? They use 1000 instead.

Cheers,

Nigel
 
The convention used to be KMGT meant multiples of 1024; kmgt multiples of 1000. Has that changed?

Interpretation of m/M for mega/milli/micro was always left to context.
 
Bloody weird...

View attachment 842536

It works now. But I have formatted the cell to display only 2 decimal places, but it is ignoring that setting as well.

I love excel and hate it in equal measures.


Just to add a solution to a problem you don't have any more..... (or sharing a bit more info)....

As soon as you use the "&" bit in a formula to join bits together it treats the answer as text (hence the earlier solution to keep it as a number but just display it as an ending), and once it is treated as text the bit you have asked it to display to 2 DP is no longer used because that only applies to numbers but what you have now is effectively text.

In the custom cell format, you can tell it how to display text - the custom formats go number +ve, number -ve, zeros and then text, all separated by a ;

So something like

0.00;(0.00);-;[red]

The other way of doing what you wanted before is to acknowledge the calculation bit of your original former is now not a number and use the text function to show how you want the "number" bit of the text to come out, so using

=text(sum(b25:27)/1000,"0.00")&" TB"

Would have shown the answer to 2 DB (from the 0.00 in the above) - but as I say, you couldn't then use it in other formular as still a text string......

There, clear as.......

:)
 
The convention used to be KMGT meant multiples of 1024; kmgt multiples of 1000. Has that changed?

Interpretation of m/M for mega/milli/micro was always left to context.


Mathematically, 1024 is correct. But often 1000 is used instead, especially when it makes their product sound bigger or faster - such as disk drives, memory sticks, broadband speeds.

Cheers,

Nigel
 
There's also mebibytes and gibibytes which are the official terms for the 'binary' units - although I've never seen them used IRL.
 
Last edited:
@Nigel. There are many things at play in the world of storage like dedup, compression, etc that make trying to figure out actual filesystem sizes a ballache. I work with VMAX, Netapp & Data Domains & the later we had 3PB of backups on 300TB... the warps my fragile little mind.
 
Mathematically, 1024 is correct. But often 1000 is used instead, especially when it makes their product sound bigger or faster - such as disk drives, memory sticks, broadband speeds.

Cheers,

Nigel
Wikipedia says, for Gigabyte, 1024 is used "particularly for sizes of RAM". For disc sizes they'll use 1000 (for GB. Though now it could be TB, which is 1000 GB).
 
Wikipedia says, for Gigabyte, 1024 is used "particularly for sizes of RAM". For disc sizes they'll use 1000 (for GB. Though now it could be TB, which is 1000 GB).

What I have read is that it should be 1000 but where easier 1024 can be used as an approximation of 1000.

RAM makes sense because it is arranged and accessed using a binary address bus, so RAM will naturally divide into multiples of 1024.

There is a certified set of prefixes to identify the use of 1024 rather than 1000 - kibi, mebi, gibi - i.e 10GiB defines that multiples of 1024 have been used rather than 1000 but in all honesty, in all my time in the IT industry, I have never seen them used.

Cheers,

Nigel
 
Mathematically, 1024 is correct. But often 1000 is used instead, especially when it makes their product sound bigger or faster - such as disk drives, memory sticks, broadband speeds.

Cheers,

Nigel
We're into angels on pinheads territory here, but so what? It's all fun. :)

With that said, mathematically, 1000 is correct. Whether capitalised or not, kilo-, mega-, etc all refer to Greek prefixes meaning multiples of 1000. There are plenty of scientific and everyday terms which use them in that way (megaparsec, kilometre, etc).

Not that any of it matters in IT. No disc drive carries an exact multiple of 1,000 bytes; I would bet they don't even carry an exact multiple of 1,024 when you allow for housekeeping storage, etc. They certainly don't offer an exact multiple in terms of useable storage. It's simply a way of comparing capacities. And line speeds are even more problematic. It used to matter, when a kilo here or there made a real difference. But who really cares today whether their disc has 2TB or 2tB?
 

The latest video from AVForums

Is 4K Blu-ray Worth It?
Subscribe to our YouTube channel
Back
Top Bottom