Excell formula for determining download balan

Microsoft Excel 2003 (full product)
March 19, 2010 at 15:35:38
Specs: Windows 7/XP Dual Boot, P4/3.1 3gig
My daughter has a Wireless Broadband plan using a USB modem and the terms are $50.00 buys her 2gig Internet usage per month with a usage rate of 2.04cents per MB.

Periodically she likes to check her balance but the answer is always in dollar terms thus:

"You have $209.06 credit. Credit expires on 29 Mar 2010 (some charges may take 48hrs to be deducted). Your current data rate is $0.02400 per MB charged per kB or part".

She would like to convert that dollar balance to a MB figure so that if she got a balance of say $120.00 what does that convert to in MBs? She would like to have a table from say $50.00 to $300.00.

I have tried to nut this out for her but to no avail. Can anyone help?

Home Page http://ewen.uuuq.com/


See More: Excell formula for determining download balan

Report •


#1
March 19, 2010 at 16:03:10
Am I missing something or is this as simple as this?

Her $ Balance / 0.024 = Her MB Balance

=120 / 0.024 = 5,000 MB


Report •

#2
March 19, 2010 at 23:01:07
Thank you DerbyDad03, you're right... seems I couldn't see the forest for the trees. In the example she has given ($209.00) how does that convert to gigabytes or a fraction thereof. I think this is where the confusion is coming in on my part. One expects a Kilo to be a 1000 but not so in the wonderful world of computing!

Regards

Home Page http://ewen.uuuq.com/


Report •

#3
March 20, 2010 at 09:51:57
Your OP has 2 different rates listed.

First you said: "a usage rate of 2.04 cents per MB"

Then you quoted: "Your current data rate is $0.02400 per Mb"

I'll assume the 2.4 cents ($0.024) is correct and use that number.

You also asked 2 different questions.

In you OP you asked: "if she got a balance of say $120.00 what does that convert to in MBs?"

In Response #2 you asked "In the example she has given ($209.00) how does that convert to gigabytes or a fraction thereof."

I'm not sure whether you want the answer in Mb or Gb, so I'll just toss out a bunch of stuff and let you decide what you can use.

In "computing" a Kilobyte is 1024 bytes.

Depending on who you ask, a Megabyte is either 1000 Kilobytes (1,024,000 bytes) or 1024^2 (1,048,576 bytes). To get an exact number, you would need to know which "definition" her ISP is using.

Depending on who you ask, a Gigabyte is either 1000 Megabytes (1,024,000,000 bytes) or 1024^3 (1,073,741,824 bytes). Again, to get an exact number, you would need to know which "definition" her ISP is using.

I'm going to take the easy way out and use 1000 Kb = 1 Mb and 1000 Mb = 1 Gb.

Since the terms of her agreement are "Your current data rate is $0.02400 per Mb charged per Kb or part (thereof)" we'll assume that usage is rounded up to the nearest Kb. In other words, if she uses 900 bytes, she'll be charged for a full Kb or 1,024 bytes.

So, at $0.024 per Mb,

50 / .024 = 2,083.333 Mb (that's your 2 Gb)

Therefore...

209 / .024 = 8,708.333 Mb

209 / .024 / 1000 = 8.708333333 Gb

We can check this by using our original "$50 for 2 Gb" numbers.

209 / 50 * 2,083.333 = 8,708.333 Mb

Now we have to deal with the "Kb or part thereof" issue.

8,708.333 Mb x 1000 = 8,708,333.3333 Kb

Since we can't use the .3333 Kb without paying for a full Kb, we'll ignore it. In other words, it can't be paid for out of the $209 so we have to "ROUNDDOWN" the answer if we want the balance shown in terms of how many Kb she can get for her $209.

OK, back to Excel...

Put 209 in A1.

In B1 use:

=$A$1/0.024/1000 for an approximate answer in Gb.

or

=$A$1/0.024 for an approximate answer in Mb

or

=ROUNDDOWN($A$1/0.024*1000,0) for an exact answer in Kb



Report •

Related Solutions

#4
March 20, 2010 at 15:10:57
Thank you DerbyDad03... you have solved my problem. A phone call to my daughter last night established that according to a booklet she received with her modem a gigabyte is 1024,000 bytes. Armed with the information you have provided I'm home and hosed.

It is a bit confusing though isn't it?

Thanks and regards.

Home Page http://ewen.uuuq.com/


Report •


Ask Question