Formula Explanation

Hi Experts,


Just need to know how This  formula works. what is the logic behind this.

Column A
50MB
60 KB
6.1 GB
60KB
223MB


Formula in B1
=SUMPRODUCT(LEFT(A1:A5,LEN(A1:A5)-2)*1024^(MATCH(LEFT(RIGHT(A1:A5,2)),{"K","M","G","T"},0)))/1024^3

Open in new window



Thank you
LVL 8
Naresh PatelTraderAsked:
Who is Participating?
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
SUMPRODUCT is an array formula. It looks for any arrays included as parameters and then expands them.

=SUMPRODUCT(LEFT(A1:A5,LEN(A1:A5)-2)*1024^(MATCH(LEFT(RIGHT(A1:A5,2)),{"K","M","G","T"},0)))/1024^3

This part:

LEFT(A1:A5,LEN(A1:A5)-2)

is pulling the numeric part of the string. It assumes that the only text in the string will be at the end and contain only two characters. Trailing spaces are ignored when converting a string of numbers to a numeric value.

This part:

1024^(MATCH(LEFT(RIGHT(A1:A5,2)),{"K","M","G","T"},0))

Is taking a power of 1024. The power is determined by result of the MATCH function which returns the index of the value matched. In this case the formula is pulling the second to the last character in the cell's value and matching it to the array of single characters. The result is a 1, 2, 3, or 4 which, when 1024 is raised to that power, adjusts the value to KB.

The final part is to divide the total by 1024^3 which converts KB to TB.

Kevin
0
 
jayakrishnabhCommented:
Final part to convert to GB, rest all as Kevin explained.
0
 
Naresh PatelTraderAuthor Commented:
Mr.zorvek (Kevin Jones),

Thank You For Replying.

The final part is to divide the total by 1024^3 which converts KB to TB.

Final Result Is "6.366716" By Applying This Formula. So It Is 6.366716 TB? I Guess It Is 6.366716 GB.

Thanks
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
zorvek (Kevin Jones)ConsultantCommented:
Yes, I believe you are correct.

Kevin
0
 
Naresh PatelTraderAuthor Commented:
Opps I dint Refresh The Browser Before Posting.

Thanks
0
 
Naresh PatelTraderAuthor Commented:
Awesome New Thing Learned !!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.