Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

(40% Discount Ends Soon!)

If "pass","fail"

How do I set the formula if

if F1...I1 <10 = Pass, >=10 Fail (and highlighted red), and any cell between F1....I1 is empty = blank


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

(40% Discount Ends Soon!)

View Course




Similar Topics







Hello,

I am new to using Excel and I am not very computer literate. I am looking for some help with a spreadsheet I am making. Here is the problem:

I have a column of 14 numbers in cells A1 to A14. If all of these values are less than 2%, I would like a "Pass" to display in cell B1. If any of these values are greater than 2% I would like a "Fail" to display in cell B1.

I have got a formula that works for a single cell but I can't figure out how to get it to apply to more than one cell. For example, I have entered =IF(A1<2,"Pass","Fail") in cell B1. Now if the value I enter in A1 is less than 2, it shows a "Pass", and if the value in A1 is greater than 2 it shows a "Fail" in cell B1. How can I get this to apply to all of the cells from A1 to A14?

If somebody could help me out that would be great.

Thanks,
Randy


I often use if statements to return empty cells, for example:

=IF(a1=0,"ERROR","")

The trailing "" returns an empty cell. The problem is, it is not TRULY empty. If I fill that formula down, I cannot, for example, jump from one "ERROR" cell to the next by hitting Ctrl arrow-down because it seems to think that these empty cells have contents.

Is there a way to designate, in such a formula as above, to return a TRULY empty cell?

Thanks


In Excel there is a difference between cells that are blank (= "") and that are empty. You can make blank cells empty by selecting them and pressing the delete key, but I need a fast way to do this on all blank cells in a large worksheet. The only way I have found is with a VBA macro that loops through every cell, tests for '.Value = "" ' and then uses the '.Clear()' function, but doing this on 30 columns x 10000 rows is far too slow. Any solutions?

(The reason I need to do this is for importing into Access, the database treats empty cells as NULL which is what I want. Blank (but not empty) cells screw the import process up.)


is it possible to put two formulasinto one cell?

On the attached timesheet there are columns IN, OUT, IN,OUT
The lunch time is worked out by deducting 'C8 from C9' and this works fine when all 'IN, OUT' cells are filled in but for some reason when just the first two cells are filled in it gives a -12 hour answer.

looking at cell 'C11 ' in the attached worksheet, I currently have the formula
=IF(C13=0,","SUM(C9-C8)) The reason for this is to make it look tidy by having blank cells until a calculation is needed,, My problem is, If someone just comes in for the morning one day for instance 8am to 12 noon then they won't have lunch, but C11 will show -12:00
Can I put another formula into C11 to tell it not to deduct C8 from C9 until a time is put into C9.
The following is the sort of thing (many variations) I have been trying but because I'm hopeless at formulas I'm not having any success
Thanks

=IF(C13=0,","SUM(C9-C8))OR,IF(C9,ISBLANK,"",SUM(C9-C8)


I am using the following formula to calculate the number of days between a date and the current date: =DATEDIF(A1,TODAY(),"d") What do I add to the formula to leave cell blank if no date is entered in the cell for calculation.


I have cells in a column, some colored yellow, some not. I am trying to use
the SUMIF function to sum only the cells in the range that are colored. Can
this be done? Can I enter something in the "criteria" part of the formula
that can do this?

=SUMIF(D3:D13,"criteria",D3:D13)

I also tried to use the CELL function's color feature, but I couldn't get it
to work right. I don't know how to get Excel to recognize if a cell is
colored in a formula.
=CELL("color",cell)

It might just be that I don't know what this means in Help:

"color" --> 1 if the cell is formatted in color for negative values;
otherwise returns 0 (zero).

Can anyone help?



Hi,

I'd like help in creating a macro that deletes an entire row that has emtpy cells in col B, C & D in the same row.
So for example if I have empty cells in b3,c3 & d3 I'd like the row deleted.

I've used the code below for just column B but I need to include column C & D as well. I tried putting Columns("B:D") but it deletes everything.

Code:

Sub Step4()
   On Error Resume Next
   Columns("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub


I'm sure it's something simple... like me !

Any help much appreciated

Wrightie


Hi, all - I have been banging my head on the desk over this one...it's gotta be so simple, right? Have searched online, and on Mr. Excel, but still can't get this right.

I need a formula in cell L3 that says "IF cell K3 is greater than 25 but less than 30, K3, otherwise, blank." Cell K3 currently contains the value 30. My formula in L3 currently is =IF(K3>25,K3,IF(K3

I have two columns in my spreadsheet. Column A has no blank fields, Column B has some blank fields. I would like to show the text from Column A in the same row of Column B only when Column B is blank.

So, basically I'm after: If column B has text, do nothing. If column B is blank, then list text from Column A.

Right now I've created Column C with this formula =IF(ISBLANK(B1), A1. That's working to get the text from A1 when B1 is blank. What can I do/add to get it pull the text from B1 if there is text there? Or...is there a better approach altogether?


I'm looking for a formula that pulls the text from a cell unti it hits a space.

I'm using the formula below but keep getting #VALUE results


B1: =LEFT(A1,FIND(",",A1,1))


I know it's not that hard but can't figure it out.

thanks for the help

max


Hi everyone,

I found an excellent macro this morning that allows the user to filter a pivot table based on the value found in a specific cell. This cell essentially acts as a search bar, allowing the user to type in what they are looking for rather than select it from a drop-down list.

The macro works perfectly for my purposes except in one regard: I can no longer perform a "show all" filter. If I leave the "search bar" cell blank, the pivot table shows nothing. I'm sure that there is an easy fix for this but I'm still learning the basics of how to write and use macros. How can I change the code so that when I leave the cell blank, the pivot table shows all? The search bar cell is D2.

Thanks for your time!


Hi, hope someone can assist, i want to add some arrow syimbols in to a formula, so that when the formula works out it shows an up arrow for higher or a down arrow for lower, i am using this formula at the moment because i dont know any better but it works, putting in the word up or down:

I have entered the following formula in to a cell: =IF(S3<R3,"DOWN","UP") it works however i want to replace the word Down and up with the relevant arrows symbol. How do i do this?

Thanks


I'm trying to come up with a solution to calculate the man hours and days worked over multiple sheets.

I have a file that has 8 sheets. the first seven sheets are the days of the week and the 8th sheet is where I want to calculate the hours.

The days of the week consists of "Name" "Date" Hours"

The 8th sheet consists of "Name", "Days of the week in their own column (7 columns in total)", "Total Hours" and "Total Days Worked"

Now... each day is always a different group and what I'm hoping for is to create a formula that can grab each name with their hours and throw them into the 8th sheet. So when the end of the week is done I have all the Employees weekly hours and days worked all finished.

I hope this makes sense. This file is to track about 100 employees that are on a rotational work schedule.

I have attached a sample file.

Thanks in advance for your help.


Hi all -

Im very familiar with how to write a macro to automatically autofilter a list based upon a Cell Value (i.e. Range =(A1)...etc.).... but what I cannot figure out how to correctly write is a way to have Excel automatically autofilter a list for any row that CONTAINS the Cell Value, instead of just the exact value.

Cell Value = "birds"
Example -- I need to filter every row so that I see every row that CONTAINS "birds" in the character-string, not just the row that = "birds".

a) is this possible?

If not, I'd like to know also so I can stop attempting to guess (-;
Thanks!!!

Matt

I have found similar posts on the forum, but nothing without use of a userform.

I have the following code:

Code:

 
Sub Development()
    Pass = InputBox("Please enter development password", "Password")
    If Pass = "XYZ" Then
        UnhideAll
    Else: MsgBox ("Password incorrect, please try again")
    End If
End Sub


This works, but I want to hide the text as it is entered.
Is there a way I can enter this as a property? i.e. inputbox.text.property = ... or something?? I've tried what seems intuitive, but nothing works.

Help!


I have a co-worker's file that he is having trouble with. He is using Excel 2000 SP3. When copying a cell with a formula in it of "=D6+C6" and pasting it into the next cell down, it will display the same value in the cell as the calculated value from above, but has the correct formula displayed in the formula bar of "=D7+C7".

Example:
A1: 50
A2: 10
B1: 60
B2: 20

A3: Formula: =A1+A2 Displays: 60
Right click A3, Copy, right click B3, paste
A3 displays 60

When I click save, it will change the display value to 80.

I am trying this on his workstation and mine. Mine has Office 2010, so I think there might be an issue with the file itself.

Also, not just copy and paste. I can also just click the top cell after filling in the formula and then drag the bottom right of the cell downward and it will do the same of filling in the correct formula, but have the incorrect value.

I know that I could get him to just click save each time before really looking at the results, but that is just a band aid to the problem.

Any ideas how to fix this?


How do I code a custom format to show 0% as a dash? I know how to make it blank, but I don't really want that....my formulas are quite complex already so I'm not sure I want to do it as part of the formula...thanks!


Hi,

Im working with a large data spreadsheet that has Yes and No answers in different columns. There are blanks within the selection too.
I need to calculate only the Yes.
I have tried using the filters in the Pivot Table field list and they are not working.
So now i'm trying to add a COUNTIF formula in the Calculated Fields section.
The formula I'm using is =Countif('Time limit extension'="yes") and i get the answer "too few."
What should I be using in the formula to get the calculated field to work? I have search the whole forum and I haven't found an answer that can help me.

Please HELP!!!


This is my first post in these forums.
I am trying to create a formula which will look at the data in columns A - I (50 rows) and copy this data to columns K - S but ignoring any blank cells. Thereby consolidating the data in the upper rows with no spaces.
I am sure there is a relativley simple answer but it is driving me mad!
Hope you "Guru's" can help

Thanks

Added example spreadsheet to aid assistance.


Hi there,

I have a spreadsheet with some cells setup with a drop-down list containing
Y, N or N/A

This is being used on a TabletPC but if I make a mistake or need to change
back to a blank field I have to invoke the soft keyboard, activate the cell
and hit backspace then close the soft keyboard - quite a long-winded
procedure just to change an incorrect choice!

What I would like to do is add a blank to the list so if I have to revert
back to a blank I can just use the stylus to choose a blank from the chooser
list.

How do I add the option of inputting a blank from the Data Validation List
bearing in mind I am using the Data Validation Source box for entering my
choices directly and not specifying a range of cells?

I have tried adding "" and even a space to no avail.

Although not a betting man I would wager there is a simple 'fix' for this
but things are only simple if you know how in the first instance! ;^)

--
Thanks & regards,
-pp-