Assigning values to a cell based on content

Hello Experts,

I'm not very knowledgeable in Excel, and I'm looking for a way to take the values in a single column, and based on their contents, assign one of 4 values in another column.

For example:

1. Column P shows a heading of "Mail Size".  The data contains letters and numbers.
2. The value of Mailbox Size in column P will show letters and numbers like 275.5M, 1.1G, 5.9G, 958.5M, etc.
3. I'll need to recalculate the values in column P with the following formula: value+40%+15%= final result, returned in Gigabytes (G) and placed into column Q.
4. I'd like to assign the result of the formula for column Q one of 4 values based on the result of the formula into column R.

So, column P value is calculated with the formula (v+40%+15%=F) and stored in column Q.  Column Q's values are checked against a lookup and based on the values, assigned 1 of 4 results in column R:

< 1GB = 1
>1GB but <3GB= 3
>3GB but <7GB = 7
>7GB =20

Am I making sense on this at all?  I'm really hoping someone can help me with this.  Thank you in advance for your help.
HelpfulAdvisorAsked:
Who is Participating?
 
andrewssd3Connect With a Mentor Commented:
Hi

Have a look at this - there are two named ranges, Factors that defines the sizes of a kilobyte, meg, gig; and Scale that defines your buckets.  Then you look up the values and assign as you requested. You could change the named ranges to account for other factors, or to changethe size/number of buckets
mailbox.xlsx
0
 
StephenJRCommented:
Could you post a workbook?
0
 
redmondbCommented:
Hi, HelpfulAdvisor.

Please see attached. The formulas are...
Q2 ==> =IF(LEN(P2)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(P2,"G",""),"M",""),"K",""))=1,MID(P2,1,LEN(P2)-1)*VLOOKUP(MID(P2,LEN(P2),1),{"K",1024;"M",1048576;"G",1073741824},2,0),P2)*1.55
R2 ==> =IF(Q2<1024^3,1,IF(Q2<3*1024^3,3,IF(Q2<7*1024^3,7,20)))

Some points...
(1) I assumed that 40% + 15% meant 55%. Otherwise, please explain.
(2) I've assumed that "K" is also a possibility and that it is 1024, while M is 1024^2 and G 1024^3.

Edit: My Q2 formula is pretty horrible. If you would permit the use of a couple of extra columns then the formulas would be considerably easier for you to maintain.

Regards,
Brian.GB-Band.xls
0
 
HelpfulAdvisorAuthor Commented:
andrewssd3,

That was perfect! Exactly what I needed.  Thank you everybody for your answers.
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.