Free Microsoft Excel 2013 Quick Reference

IF, OR, Statements from non-numerical values


I am trying to manipulate data contained in an xls that has been, until now, updated without much formatting. I have updated all the cells in a column named "Duration" to contain a drop list with the following values:

All day
AM only
PM only

These values actually equal payable time spent completing a task (hours and minutes) so:

All day = 7hrs
AM only = 3hrs 30mins
PM only = 3hrs 30mins
Evening = 7hrs

I want to create a column that determines the appropriate number of hours from the user selection in the column "Duration". For example, user selects "All day" in a cell within "Duration" column, and the next column is updated with "7".

I've attempted an IF statement "=IF(C2="All day", 7)", which worked for 1 value, but I'm having trouble with additional variables - can anyone help?


Post your answer or comment

comments powered by Disqus
I'm looking for a formula that would give me this:

If A1 < = .3 then return "30% < 40%"; if A1 is > = .4 then return "> = 40%"

I hope all that makes sense. Essentially I'm looking at a If(or statement with one more return value than a true/false option.

Hello all,

I am trying to create a macro that will delete the entire row if column A contains non-numeric data or is blank.
I am importing data from a different spreadsheet that has column headers or sometimes is blank in column A but contains data in other columns. So I would like to only keep the row if column A contains numeric data.

Your help is greatly appreciated.

Long time reader, first time poster.

I am trying to conditionally sum numbers from a matrix. On the vertical axis, there may/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).

The vertical and horizontal axis labels will be changing and I will also be imputing different variables to pull from in the fields below, so said another way, this needs to be dynamic. I will be pasting data into this table over and over again for different reasons and I will need to go through and assign letters to each row and sometimes the rows will have the same letters and sometimes not and sometimes I will need to sum "A" rows and sometimes "C" rows etc between different numbers.

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 picture I have attached, I want to sum rows for the letter "A" and in between numbers "2" and "4." I have also attached the excel file.

Thanks and looking forward to one of you gurus helping me out.

Excel Screen.png

I want an error message to appear if one of the textboxes (20 to 38) has a non numeric value. But if its blank or got a number in then to do nothing.

Any thoughts.
This is what i have at the moment. All it does is display ("Please enter a numeric value") no matter whats in the textboxes.

For Each change In Changequan.Controls 
    If TypeOf change Is MSForms.TextBox Then 
        For c = 20 To 38 
            If change.Name = "TextBox" & c Then 
                If change.Text = nonnumeric Then 
                    MsgBox ("Please enter a numeric value") 
                    Exit Sub 
                End If 
            End If 
    End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks Adam

I am sure this is really easy, but I can't seem to figure it out. I see alot of posts on 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.

I want an error message to appear if one of the textboxes (20 to 38) has a non numeric value. But if its blank or got a number in then to do nothing.

Any thoughts.
This is what i have at the moment. All it does is display ("Please enter a numeric value") no matter whats in the textboxes.

Dim change As Control
For Each change In Changequan.Controls
If TypeOf change Is MSForms.TextBox Then
For c = 20 To 38
If change.Name = "TextBox" & c Then
If change.Text = nonnumeric Then
MsgBox ("Please enter a numeric value")
Exit Sub
End If
End If

End If

Thanks Adam

Hi everyone,

I have a master list of client numbers, and want to filter subsets of clients based on their client number (example below). I already have the desired client numbers listed in columns.

I think my goal would be best described in an example

Column A: List of numbers from 1-1000
Column B: Subset of any numbers between 1-1000 (ie. 34, 203, 498, 939)
Column C: Beside the numbers in column A that match the numbers in column B (but aren't nescessarily in the same row) I need a 1 returned, and beside numbers in column A that do not have a match in column B I would like a 0 returned. To clarify If B1 = 34 I would like to return the value 1 in cell C34 (because A34 = 34).

Basically I want to compare the master list to a "desired client # list", and if the number in the master list matches the number in the desired client # list, return a 1, if the number in the master client list doesn't have a match in the desired client # list return a 0. I need the returned values (0 and 1) put in the same row as the values from the master list.

Overall, I want to use the 1's and 0's to filter the columns containing the matching numbers.

I haven't been able to get IF/OR statements to work. I am comfortable with IF statements on their own, but linking them to OR statements is a challenge to me.

Don't know if this is the best approach, but any suggestions would be marvellous.



What is the easiest way to remove non-numerical values from a row of cells for processing?

I'm creating a timesheet for technicians in my service department. I have a
team-based system where technicians split the total hours their team does by
the number of techncians on the team, which is 4 per team. It currently
takes the hours in a top cell and automatically divides the hours into the
technicians rows below (e.g., team turns 40 hours in a day, each technician
gets 10). Is there any way to exclude a cell if it contains a non-numeric
value (e.g., "VACATION" or "SICK"), and recalculate by the number of open
cells left?


I'm trying to copy non-numerical values to a column from data in other columns. Here's an example...

I have fixed data in Columns B,C and D.

*Column B
Row 1: Red
Row 2: Green
Row 3: Blue
Row 4: Orange

*Column C
Row 1: Apples
Row 2: Oranges
Row 3: Bananas

*Column D
Row 1: To eat
Row 2: To bake
Row 3: To fry
Row 4: To rot

Now, In Column A I want to create the following dynamically...

Row 1: Red Apples
Row 2: Red Apples to eat
Row 3: Red Apples to bake
Row 4:Red Apples to fry
Row 5:Red Apples to rot
Row 6:Red Oranges
Row 7:Red Oranges to eat
Row 8:Red Oranges to bake
Row X:Green Apples
Row X:Green Apples to eat

I was trying to use the "SUBSTITUTE" formula, however, I didnt have any success. Does anyone know which formula to use to create this list in Column A?


i'm trying to build a chart using two data ranges. The y-axis is numeric value, but the x-axis is non-numeric (corporate credit rating, like AAA, AA+, AA- and so on) in decending order, with AAA the best and AA+, AA, AA- and so on. Is it possible to build a chart with non-numeric values?

Surely, i could replace this characters with numbers, but then the chart would display numeric characters and is not easy to read.

Do you have any idea how to solve this problem?


OK i having some trouble trying to figure out a IF OR statement.
Attached sheet.
has defined some NAME ranges.

have task named range
Task Task Points
Dishes 10
Rubbish 10
Clean House 20
Kitchen 15
Bathroon 20
Own Room 25

has a cell on the main sheet linked to list called TASK
if the person selects DIshes I want the corresponding task points to go in the next cell.

OK guys and gals, really need your help.

Basically what I am trying to do is produce a formula where:

If N4 = 1 or N4 = 2, P4 will be equal to 10
If N4 = 3 or N4 = 4, P4 will be equal to 9
If N4 = 5 or N4 = 6, P4 will be equal to 8
If N4 = 7 or N4 = 8, P4 will be equal to 7
If N4 = 9 or N4 = 10, P4 will be equal to 6
If N4 = 11 or N4 = 12, P4 will be equal to 5
If N4 = 13 or N4 = 14, P4 will be equal to 4
If N4 = 15 or N4 = 16, P4 will be equal to 3
If N4 = 17 or N4 = 18, P4 will be equal to 2
If N4 > 18, P4 will be equal to 1

I have tried loads of different If and, if or statements, and I can't seem to get any of them to work.

Going bald through pulling my hair out at this stage, so your help would be greatly appreciated.

Probably very basic question
How to randomely distribute non-numerical values across rows (smth like the
randbetween() but not for numbers=? For example I have to randomely
distribute the 50 U.S. states names in 1 column/100 rows...

How many IF OR statements can one have in one formula?

I need 12 and if I can only have a MAx of 7, do I just split over 2 cells?


I want to search a non-numeric value in a range. Can we perform this with single line of execution and not by checking each and every cell with a For Loop concept.


Hi All, am new to the board. Have been looking for sometime and it's been very useful.

I have an issue with 'AND' 'IF' 'OR' statements.



B2= Y OR A


H2= Y OR N


J2= ""

The answer should be 1

The equation I am using is as follows but cannot work out where I need the second 'AND' 'OR' Statment;


I can filter the sheet to give the answer I need, but this is not convenient as there are 3 spreadsheets with the same calculation on them which are then linked to a 4th.

Thanks in advance!

I need to plot non-numeric values in a graph in Excel.

Assume I have the following data... :

X Axis Y Axis
I want such a graph

R x
Q x
P x
O x
N x
M x
L x

i.e. ABCD on the x axis and lmno on the y axis and the small x points are
the plots of AL, BM, CN etc..


I am trying to change the x-axis of my bubble chart to non-numeric values - File is attached for your reference.

How do I do this? I use Excel 2007 and have tried all options but to no avail. I am basically trying to replace the 1,2,3 etc (default values) on the x-axis of the bubble chart with say (Apple, HP, Samsung, Intel etc).

Please help.


I am trying to create a template where data can be downloaded into a spreadsheet and graphs duly created. I am using formula to calculate values for these charts only if certain cells are not zero values. This is fine, but the graph function in excel recognises non-numerical results from formula as zero. How do I get round this ... (I may have asked this before, but can't work out how to pull up a history of my own posted enquiries)


Hello again..

I need help formatting an statement..
basically its an if/or statement mixed in with a median statement..

here is what i have currently:


what i am trying to do is nest another if in there.. like right now it checks the d column for the value in a9634 i need to add to this statement to check another column but i cannot figure it out.. the other column is "DZ" with the same rang and contains numbers from 1-50

if anyone could help it would be great.


Thanks for the last replies, got that bit working:-


columns(6).SpecialCells(xlConstants,xlTextValues). ClearContents

Manually, select the column, do Edit=>Goto => Special and select constants
and text

then edit=>Clear=>Contents

Tom Ogilvy

Mike > wrote in message
> I have a column in which a custom equation is entered. This equation
> is defined by a macro (which I didn't create) and outputs the result
> in the cell. Upon evaluating this column of equations, I am using
> another macro to plot the data. The problem is that for some inputs,
> the equation cannot produce a result and therefore displays a custom
> error message that looks like:
> [PSFLSH error 1]; one or more inputs are out of range: temperature >
> 1.5 x upper limit, T = 1057.3 K, Tmax = 582.93 K
> Since it is not an Excel error message, I don't think the normal
> methods of searching for an error will work. Once the error occurs,
> then for every row after it will also contain this error. So I want
> to somehow have the macro identify where the error starts, and then
> delete the contents of the cells from that point forward. Then I
> should be able to plot the remaining data, even though I'll still be
> referencing these blank cells. If I plot the entire range with these
> error messages, then the plot shows the line dropping to zero from
> then on which is something I don't want.
> So how can I search through a column of data and delete only those
> cells that contain non-numerical values? Any help would be greatly
> appreciated. Thanks.
> Mike


I wonder if someone can present a VBA (or any other solution) how to browse an entire document (full of text and numbers) and when the selected word is a numeric value (can be 123 or 06) to add X which will be inputted by the user via a Inputbox command.

Hopefully, such a solution will be capable also to add the same X to a typed Date (to increase the day only in a non date field type)

It will be nice if the macro will stop at any founded numeric value and ask weather to add or to leave the value intact and move to the next word.

Any suggestions will be more that appreciated.

I managed to write a short code but it does not work as expected.

If X = 3 and the found value is 5 - the result gets to be 53
Sub IncreaseMe()
On Error Resume Next
X = InputBox("Enter Number to be added: ")
WC = ActiveDocument.Words.Count
W = 1
Do While W <= WC
   If Val(Selection) > 0 Then
      ANS = MsgBox("Do you really want to increase by " & X & " ?", vbYesNoCancel)
        If ANS = 7 Then GoTo L
           Selection = Selection + X
   End If
  W = W + 1
L: Loop
End Sub
Thanks, Elm

No luck finding an answer? You could always try Google.