[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

# Formula Explanation

Posted on 2014-03-19
Medium Priority
343 Views
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
0
Question by:Naresh Patel
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2

LVL 81

Accepted Solution

zorvek (Kevin Jones) earned 2000 total points
ID: 39941549
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

LVL 5

Expert Comment

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

LVL 8

Author Comment

ID: 39941561
Mr.zorvek (Kevin Jones),

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

LVL 81

Expert Comment

ID: 39941566
Yes, I believe you are correct.

Kevin
0

LVL 8

Author Comment

ID: 39941567
Opps I dint Refresh The Browser Before Posting.

Thanks
0

LVL 8

Author Closing Comment

ID: 39941569
Awesome New Thing Learned !!!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
###### Suggested Courses
Course of the Month13 days, 11 hours left to enroll