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

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 883
  • Last Modified:

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.
1 Solution
Could you post a workbook?
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.


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
HelpfulAdvisorAuthor Commented:

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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

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