Hi,

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

Evening

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?

Cheers!

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

Evening

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?

Cheers!

- IF(OR statement with three return values
- Delete Row if column A contains non-numeric data or is blank
- Conditional Sum from a Matrix with Non Numerical Values
- Creating Error Message for Non Numeric Value!!
- Filtering & Deleting Non Numerical Values Throughout Worksheet
- Creating Error Message for Non Numeric Value!!
- Help with linking IF/OR statements
- Remove non-numerical values from large numbers of cells
- NON-TEXT VALUES IN CELLS
- Copying (non numerical) Values To Cells
- Non-numeric values in Charts
- If OR statement
- Nested IF OR statement?
- How to randomely distribute non-numerical values across rows?
- How My If Or Statements
- Search a non-numeric value in a range
- And If Or Statements?!?!?
- How do I plot non-numeric values in a graph in Excel?
- How to change x-axis of bubble chart to non-numeric values?
- Charts: Ignoring non-numerical values in Source Data Range
- Help with if/or/and type statement....
- IF & OR STATEMENTS again!
- Checking for non-numerical content in a cell
- Add X from every numeric value

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.

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.

Thanks!

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

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.

VB: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 Next End If NextIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks Adam

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

Next

End If

Next

Thanks Adam

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.

Thanks,

John

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

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

individual

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?

cheers,

korsar

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.

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.

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...

Thanks

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.

Thanks

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

Essentially;

IF

B2= Y OR A

AND

H2= Y OR N

AND

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;

=IF(AND(OR(B2="Y",B2="A")$H2=Y,$J2=""),1,0))

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!

Assume I have the following data... :

X Axis Y Axis

A L

B M

C N

D O

E P

F Q

G R

I want such a graph

R x

Q x

P x

O x

N x

M x

L x

A B C D E F G

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.

Thanks....

Thanks

I need help formatting an statement..

basically its an if/or statement mixed in with a median statement..

here is what i have currently:

=MEDIAN(IF(($D$3:$D$9628=$A9634),E$3:E$9628))

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

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

and text

then edit=>Clear=>Contents

--

Regards,

Tom Ogilvy

Mike > wrote in message

om...

> 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 ActiveDocument.Words(W).Select 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 SubThanks, Elm