# Applying Numerical Values To Letters In Formula

Feb 28, 2007
I'm putting together an Environment Threat Assessment for work and want to automate as much of it as I can to alleviate the amount of time ppl have to spend putting values in. My main problem is I have three very important columns: Likelihood, Success Rating and Risk Rating. Likelihood and Success Rating values will be selected from the following:

VL - Very Low

L - Low

M - Medium

H - High

VH - Very High

Each of these is given a value: VL = 1, L = 2, M = 3, H = 4, VH = 5.

The Risk Rating is the result of multiplying the Likelihood and Success Rating. So a Likelihood of Medium and a Success Rating of Low will give a Risk Rating of 6. Therefore, the cells on my spreadsheet would look like this:

Likelihood Success Rating Risk Rating

M L 6

The issue is I want a couple of things to occur. Firstly I want keep the values in my Likelihood and Success Rating cells as letters (ie. VL, H, VH etc) rather than numbers, while still generating a number result in the Risk Rating cells. Secondly I'd like to be able to change the colour of the Risk Rating cell to reflect the number it gets assigned. For example Green for < 8, Amber for 9 – 14 and Red for 15 – 25. I've been trying to do this with array formulas and am basically going around in circles with no success.

View 3 Replies
ADVERTISEMENT
Jun 8, 2014

I want to apply a simple formula to a number of cells in an existing workbook that already contain values E.g.

My column contains the values

5200

1600

4376

in separate cells

I want to divide each value by 1.2 - without having to change every individual cell - can it be done?

View 2 Replies
View Related
Mar 8, 2013

I'm working on an email database and am looking to sort them into type. I am trying (without success) to use the IF function as follows.

If A1 contains the letters "isd" consecutively, then B1=TRUE

If A1 contains the letters "google", "yahoo", "hotmail", or "austin.rr" consecutively, then B2=true

View 4 Replies
View Related
Dec 12, 2009

I'd like to find out the formula where numerical valued between 1-2000 equals True and numerical under 0 equals False. Examples are in the attachment.

View 3 Replies
View Related
Apr 30, 2014

I am quite inexperienced with Excel and I am struggling to understand how should I set up a formula, within the data validation function to achieve the following:

I need to restrict a certain set of cells, for people to be able to input only the following values: "1", "0.5", "A", "H", "0.5A" (or 0.5a), "0.5H" (or 0.5h)

And I would need the data validation tool not to include blank cells.

View 1 Replies
View Related
Oct 29, 2008

formula to add letters but with a numeric value. this is for a schdule sheet. where w would equal 7.5 and x would be 0.

i am using this

=SUMPRODUCT(--(ISTEXT(B3:H3)))*7.5

reads the w and adds up ok but need to be able to put w for work and x for off days and still add the total hours

View 5 Replies
View Related
Mar 3, 2014

I need to input a formula that will produce a "yes / no" result if a "0" value appears consecutively six or more times.

Using the date below (ranges A1:Q1, A2:Q2 and A3:Q3), I need cell R1 to read "yes", as there are 7 consecutive "0" values. Cells, R2 and R3, however, need to read "no" as there are less than six consecutive "0" values in rows 2 and 3 respectively.

A

B

C

D

E

F

G

H

I

J

[code].....

View 8 Replies
View Related
Feb 20, 2012

I have an excel sheet where I do a simple price-discount calculation for a product. Please check the file at:

Code: [URL] ........

A2 shows the price, B2 shows the discount and C1-L1 shows the number of units sold. C2-L2 is where I want to calculate the earnings based on the formula I have in C2. I want to apply the same formula to the entire row so that I can quickly see the earning for any number of units sold.

View 3 Replies
View Related
Feb 8, 2014

So right now I have a formula that I'm using in column "C":

=IF(B4="","",IF(AND(B4=22),"IN RANGE","OUT OF RANGE"))

So basically column C tells you if the number in the B column in within the range of 22-30. Now I want to write the formula in column D and make it apply to columns B and C.

So cells in columns B and C will have a number value in it and I will make column D where the formula is. There will be a number in B or C, but never at the same time. I can't figure out how to alter the formula to do this.

View 3 Replies
View Related
Feb 14, 2013

Cell B1 is to display money value dependent on what item has been selected from drop down box in Cell A1

View 2 Replies
View Related
Jul 25, 2009

creating functions in excel and would like to hire someone who can assist me in creating the following functions: Column A (For Numerical Values Less Than or = to 100): Column B Value should be (+10)

Example 1: If a numerical value of 90 is placed in Column A then Column B Numerical Auto Value should be 80+10 (190). Example 2: If a numerical value of 35 is placed in column A then Column B Numerical Auto Value should be 35+10 (45) and so on. Another important point is that if the values in column A are copy and pasted Column B should automatically discharge the values visibly as the examples stated above is this possible? If not i'll need to have this done.

Also there are other numerical values that must be added in Column A (basically for every increase in numerical value of 100 there are to be different numerical plus factors in accordance to Column A). Column A (For Numerical Values greater than 100 but less than 200): Column B Value should be (+15).......

View 4 Replies
View Related
Jan 25, 2010

I want to set up an if statement that does the following, where 'a' and 'b' are numerical values.

If 'a' is not a multiple of 'b' Then

...

Else

...

End If

How can I write this so VBA can understand it?

View 2 Replies
View Related
Apr 6, 2006

I've got a series of values in a column as follows:

10 Mb

10 Mb

1000 Mb

1000 Mb

114 Mb

128 Mb

128 Mb

Obviously 1000 mb is more than 10 mb, but it sorts as second in the list

because it starts with a "1". How do I tell Excel to sort by the entire

numerical value instead of the first number?

View 9 Replies
View Related
May 11, 2009

it seems that excel is deciding what type of array this is by evaluating the type of the first cell in each column and applying this type to the rest of the array's column. Problem is that i want all values to be brought in, text and numerical.

Dim VArray As Variant

Sub DA201()

If FileThere(Path & "DA201.xls") Then

GetDataTowers Path & "DA201.XLS", "", "DataRange", Sheets _("Towers").Range("CStart"), False, False

Else

MsgBox ("File DA201.xls not found. Click OK to continue")

End If

End Sub

Public Sub GetDataTowers(SourceFile As Variant, SourceSheet As String, SourceRange As String, TargetRange As Range, Header As Boolean, UseHeaderRow As Boolean)

Dim rsCon As Object

Dim rsData As Object

Dim szConnect As String

Dim szSQL As String

Dim n As Integer

Dim row, m As Integer

If Val(Application.Version) < 12 Then

szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & SourceFile & ";" & _

"Extended Properties=""Excel 8.0;HDR=No"";"

Else....................................

View 9 Replies
View Related
Jul 23, 2014

I've applied a basic multiplication formula =D12*E12 in a table Column F, also I've filled it throughout but now if new rows are inserted in between or at the end of the table, new cells in this columns doesn't include this formula.

View 1 Replies
View Related
Mar 3, 2014

I have a formula that I wrote in B2 and then applied vertically to a large list of rows (100+). I applied it vertically by simply double-clicking the cell with the formula, and it cleanly applied the formula to every row in Column B.

This formula now needs to be applied horizonally across columns, for each respective row. I need a way to do this in one swoop, as it would be very time consuming to manually drag each cell in column B.

I've attached a sample spreadsheet with a simple formula just for the sake of an example.

View 3 Replies
View Related
Jan 1, 2009

I have a range, A1:A10 that I want to sum, min, and max. I want to ignore any text or #DIV/0! values in this range. I know how write the formulas except how to ignore the text and cell errors. Can someone steer me in the right direction?

View 5 Replies
View Related
May 29, 2009

I need to convert levels to numerical values and then: Firstly, add together two vlookup values THEN divide by 2 to get an average AND THEN see if this average AND a second, individual lookup value are above a specified another value, which may be different. IF all these criteria are set, return, "yes" if either the first or second, or both criteria are not met then "no"

Or put it another way. if lookup values A+B/2>"5" AND C>"3" then "yes", Else "no"

Lookup chart:

P11

P22

P33

P44

P55

P66

1c7

1b9

1a11...............

View 3 Replies
View Related
Mar 9, 2013

I'm making a spreadsheet for the homes I'm looking at purchasing and wanted a way to calculate automatically which one has the most things we're looking for.

So for example, if a home has a walk in closet, it would say "yes". If it doesn't it obviously would say "no".

Is there a way to assign a number value in a totals column where "yes"=1 and "no"=2?

Or a way to make colors equal a certain value?

Where I'd make all the "yes" items green and then a green cell = 1, a yellow cell = 2, and a red cell = 3.

View 5 Replies
View Related
Feb 27, 2012

I am trying to conditionally sum numbers from a matrix. On the vertical axis, there will be duplicate values (text) who's rows should be summed, and on the horizontal axis I need to sum in between two numbers that will be in sequential order (such as dates). The real tough part for me is that the data field that I will be summing from has non numerical values, like dashes. Obviously, I don't want to sum the dashes, but it throws off any formulas I have tried (like sum arrays).

My hopes are to use as little memory as possible so my file size wont get too big and to not have to use macros (I do not really use them), although I am willing to try if they are basic.

In the example below, I want to sum rows for the letter "A" and in between numbers "2" and "4."

# 1 2 3 4 5

A2 6 5 4 2

B4 5 6 4 1

C-----

A4 2 1 2 1

E1 1 2 2 2

Y Variable: A

X Variable Min: 2

X Variable Max: 4

Correct Result = 20.00 = (6+5+4+2+1+2)

View 3 Replies
View Related
Mar 19, 2013

I often have to OCR files in Adobe and then copy the data as a table into Excel. Sometimes when I move the data over these little boxes with question marks inside will appear infront of the data in the cells. I can delete the square from the spell by just backspacing after it but I don't want to have to do that for hundreds of cells individually. Googling around I found a formula: =RIGHT(A1, LEN(A1)-1)

That formula will get rid of the box, once the box is removed I need to be able SUM the values, but I am still unable to. I've tried to copy and paste the values before using the SUM function but that does not work, I've also tried to SUM before removing the box but that does not work either. I've found that if I double click in the cells after using the formula that it will allow me to use the SUM function but that would require me to double click hundreds of cells individually. Also, just in case this matters, a little green flag appears in the upper left corner of the affected cells after I use the previously mentioned formula.

View 4 Replies
View Related
Jul 9, 2006

Is it possible once you have assigned text a numerical value (example: Adam = 12) to add them together? (example: adam =12 and bob = 8, therefore adam + bob = 20)

View 9 Replies
View Related
Feb 20, 2014

I'm new to creating macros and trying to create a macro that will perform a formula that I can use for multiple files. However the files are a varying number of rows.

I want the formula to carry out on the entire column (all rows). However when I record the macro (I tried using relative and also tried using absolute references when filling the formula) the formula always seems to stop filling wherever my original file's rows stopped. I wrote the formula for the first row and double clicked it to fill the remaining rows.

For example if the macro I recorded in File 1 goes to row 15, when I run the macro on File 2 which is 20 rows, the formula stops filling at row 15.

View 8 Replies
View Related
Jul 29, 2014

I'm wondering if it's possible to have a formula applied to an entire column without adding unnecessary length to the spreadsheet until someone is actually scrolling down and using more rows. I want formulas to be constantly present in two columns to automatically calculate values as they are entered into other cells in the same row, but ideally without the otherwise empty spreadsheet being X amount of rows long.

I initially had the formula dragged down 15,000 rows, but it would really be much better if it could start out as a smaller size and then expand as the user requires.

View 9 Replies
View Related
Feb 5, 2014

I am attempting to count from a spreadsheet the reference number of a customer (numbers and text) based on two criteria.

1, If column G= Requested

2, Column I = Meeting

Count Row E

I thought a sumproduct was best and have started using it for the first time, I thought this should work but I keep getting a #NUM! error.

I have tried with numbers and it works but the non numeric aspect is difficult.

[Code].....

View 14 Replies
View Related
Oct 16, 2008

I am trying to do is a summary of numerical values, based on several conditions.

column a column b column c

local loop live cross connect

loop A cancel 100

long haul live 10000

long haul pend canc 1000

loop Z live 100

x-conn live cross connect

In column a, i have several choices and i want to focus on those choices that fall into column B "live" or "pend canc" and then only have numerical data in column C

I've tried several different formulas like

=SUMPRODUCT((a30:a38="Loop A")*(a30:a38="Long Haul")+(b30:b38="Live"),cX30:cX38)

I know the result of this should be 111000 but it's not working...I usually get n/a. I know about using cntl-shift-enter to use the formula properly.

I can get one variable to sum up properly using =SUMIF(M9:M21,"Live",BW9:BW21)

but, that leaves out anything that is pend-canc

I have also tried =SUM(IF((a9:a21="long haul")*(b9:b21="live"),cX9:cX21)) which gives back a value that is accurate.....

View 8 Replies
View Related
Aug 28, 2009

I have the following 'numerical values'

2510000.011

3010000.011

3510000.011.3

2518000.032.5

3018000.043

3518000.043.7

2530000.125.5

3030000.145.8

3530000.176.2

With the first two values as criterias, I need to find the 3rd and 4th value

Example: If I have the criteria as 30 and 1800 , I should get the result as 0.04and 3

View 10 Replies
View Related
Nov 21, 2008

Hi all, first post here.

I've been asked to do something in excel i can't seem to get my head around.

My client provides me with data in the form

1c, 1b, 1a, 2c, 2b, 2a, 3c etc... where 1a is the lowest.

I need to find a way of working out the difference between these, e.g.

edit: 2a - 1c = 5

I've been working on it and have made some headway by splitting these with MID function and then using CODE to try and work out a unique ID for each, but i can't help feeling there's an easier way!

View 9 Replies
View Related
Jan 31, 2010

I am working up a workbook template that will be used to process survey results. The way I ultimatley want it to work is that the user can paste data from another application into a worksheet in Excel, and then view analysed results on a second sheet (which are processed via formulae on a third, hidden sheet).

The problem I have is this: the data from the program that's capturing them are text values. For example, column B contains answers to a question where participants rate an experience, and the values are either "Excellent", "Good", "Fair", "Poor", "Awful". I want these to be converted to numerical values, on a scale where "Excellent" = 5 and "Awful" = 1, so that I can then average these.

So, the question is: Can I somehow tell Excel that "Excellent" = 5 and "Good" = 4 etc, and then use AVERAGE(B:B) on the text data and get a number back?

I did think of having an intermediate sheet that used VLOOKUP to create a copy of the first sheet (where the user pastes the text data) with the text replaced by numbers. But, because I don't know in advance how many rows will contain data (i.e. how many survey results there will be), I have to assume on the high side and copy down 50,000 rows. But, this takes ages to calculate.

View 9 Replies
View Related
May 11, 2006

how to find a certain criteria and delete the entire row that the criteria is in. I am more interested in finding certain criteria with vba, in my case any text/non numerical values and clear contents from that cell for the entire worksheet while retaining the rest of the cells that have numerical values in them. My data has --- in cells that represents missing data which would be easier if it was completely blank.

View 2 Replies
View Related