Assigning values to a cell based on content

Posted on 2012-08-22
Last Modified: 2012-08-26
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.
Question by:HelpfulAdvisor
    LVL 24

    Expert Comment

    Could you post a workbook?
    LVL 26

    Expert Comment

    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.

    LVL 17

    Accepted Solution


    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

    Author Comment


    That was perfect! Exactly what I needed.  Thank you everybody for your answers.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    This collection of functions covers all the normal rounding methods of just about any numeric value.
    Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now