[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 350
  • Last Modified:

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
0
Naresh Patel
Asked:
Naresh Patel
  • 3
  • 2
1 Solution
 
zorvek (Kevin Jones)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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now