Refining my function from Static to computed values

[jesterspet]

I would like to refine my Lookup function from using static values to computed ones.

The function in question takes the value of a cell (in this case A5) that is given in raw bytes, and converts it into a more human readable format (e.g. 1.46KB, or 2.06GB rather than 1500, or 2211908157).

=ROUND(VALUE(CLEAN(A5)) / LOOKUP(VALUE(CLEAN(A5)), {0,1024,1048576,1073741824,1099511627776}, {1,1024,1048576,1073741824,1099511627776}), 2) & LOOKUP(VALUE(CLEAN(A5)), {0,1024,1048576,1073741824,1099511627776}, {" Bytes"," KB"," MB"," GB"," TB"})

I would like to replace the larger numbers (1048576,1073741824,1099511627776) with computed values like POWER(1024,3) as the computed value is easier for me to read and remember. When I try to replace those static values with VALUE(POWER(1024,3) or N(POWER(1024,3) the formula no longer performs as intended and I get results like 221190 KB Instead of 2.06GB.

Could I get some enlightenment either on why this won't work, or how to do this correctly?

 

[Peter_SSs]

Would something like this suit you?
I haven't added all the CLEAN() functions that would be required so the formula would get considerably longer.
Or better, could you put =CLEAN(A5) in a separate cell then refer the below formula to that new cell?

=ROUND(A5/(1024^IF(A5<1024,0,IF(A5<1024^2,1,IF(A5<1024^3,2,IF(A5<1024^4,3,4))))),2)&IF(A5<1024," Bytes",IF(A5<1024^2," KB",IF(A5<1024^3," MB",IF(A5<1024^4," GB"," TB"))))

 

[Peter_SSs]

.. or this one would require less CLEAN()s if you want to do it directly and this nomenclature is any good to you

=ROUND(A5/CHOOSE(MIN(ROUNDUP(LOG(A5,1024),0),5),1,1024,1024^2,1024^3,1024^4),2)&CHOOSE(MIN(ROUNDUP(LOG(A5,1024),0),5)," Bytes"," KB"," MB"," GB"," TB")

 

[AlphaFrog]

One more...

=ROUND(VALUE(CLEAN(A5)) / 1024^INT(LOG(VALUE(CLEAN(A5)),1024)), 2) & CHOOSE(INT(LOG(VALUE(CLEAN(A5)),1024))+1," Bytes"," KB"," MB"," GB"," TB")

In your original formula, you can't replace the constants in the array with calculated values.

 

[Peter_SSs]

One more...

That may very well be what the OP wants, but I read it that they wanted to be able to see the various powers in the formula so deliberately went the longer routes.

I would like to replace the larger numbers (1048576,1073741824,1099511627776) with computed values like POWER(1024,3) as the computed value is easier for me to read and remember.

Otherwise I'm thinking there would be no reason to even post the question as the original formula appears to be working fine as is.

Anyway, let's see what the OP has to say.

 

[jesterspet]

Thank you Both!
I figured that there was some issue with trying to replace static in the array with computed values, but I could not find any documentation about it, nor could I get my head to successfully look for an alternate way of deriving the same results from the input.


Each of these three formulas have their merits
Peter_SSs first solution gives a en hit upon directly by a value (e.g. 1048576 would be 1024 KB) and a #NUM error when empty or given a value of 1.
The second solution results in 0 bytes when given an empty value
and AlphaFrog's solution will give an #VALUE error when empty, but otherwise performs as expected.


I think I will use Peter SSs's second formula, and try to commit this to memory for future use.


The use of the CLEAN() function was due to the original data being imported data and sometimes having non-printable characters in the number field and excel thinking it is text. I want to keep the original data imported as unprocessed as possible and simply reference it for the spreadsheet.
Also I have gotten into the habit of not relying on excel to apply the correct order of precedence to math formulas as it sporadically gives erroneous results, hence my prolific use of the POWER() function here.


Here is the end result of the effort given by the all stars and me:

=ROUND(A5/(POWER(1024,IF(A5<1024,0,IF(A5<POWER(1024,2),1,IF(A5<POWER(1024,3),2,IF(A5<POWER(1024,4),3,4)))))),2)&IF(A5<1024," Bytes",IF(A5<POWER(1024,2)," KB",IF(A5<POWER(1024,3)," MB",IF(A5<POWER(1024,4)," GB"," TB"))))

 

[jesterspet]

<cannot edit my inital respone >

Thank you Both!
I figured that there was some issue with trying to replace static in the array with computed values, but I could not find any documentation about it, nor could I get my head to successfully look for an alternate way of deriving the same results from the input.


Each of these three formulas have their merits
Peter_SSs first solution gives a 1024 when hit upon directly by a value (e.g. 1048576 would be 1024 KB) and a #NUM error when empty or given a value of 1.
The second solution results in 0 bytes when given an empty value
and AlphaFrog's solution will give an #VALUE error when empty, but otherwise performs as expected.


I think I will use Peter SSs's second formula, and try to commit this to memory for future use.


The use of the CLEAN() function was due to the original data being imported data and sometimes having non-printable characters in the number field and excel thinking it is text. I want to keep the original data imported as unprocessed as possible and simply reference it for the spreadsheet.
Also I have gotten into the habit of not relying on excel to apply the correct order of precedence to math formulas as it sporadically gives erroneous results, hence my prolific use of the POWER() function here.


Here is the end result of the effort given by the all stars and me:


=ROUND(A5/(POWER(1024,IF(A5<1024,0,IF(A5<POWER(1024,2),1,IF(A5<POWER(1024,3),2,IF(A5<POWER(1024,4),3,4)))))),2)&IF(A5<1024," Bytes",IF(A5<POWER(1024,2)," KB",IF(A5<POWER(1024,3)," MB",IF(A5<POWER(1024,4)," GB"," TB"))))

 

[jesterspet]

Formula got truncated and I cannot edit my previous posts

=ROUND(A5/(POWER(1024,IF(A5<1024,0,IF(A5<POWER(1024,2),1,IF(A5<POWER(1024,3),2,IF(A5<POWER(1024,4),3,4)))))),2)&IF(A5<1024," Bytes",IF(A5<POWER(1024,2)," KB",IF(A5<POWER(1024,3)," MB",IF(A5<POWER(1024,4)," GB"," TB"))))