# 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
``````

Thank you
LVL 8
TraderAsked:
###### Who is Participating?

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

Commented:
Final part to convert to GB, rest all as Kevin explained.
0

TraderAuthor 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

ConsultantCommented:
Yes, I believe you are correct.

Kevin
0

TraderAuthor Commented:
Opps I dint Refresh The Browser Before Posting.

Thanks
0

TraderAuthor 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.

## Already a member? Login.

All Courses

From novice to tech pro — start learning today.