Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Return a null value

I am using an '' IF '' Function in Excel. When the argument is false,
I would like the argument to return a blank value instead of a '' 0 ''
value.

Does anyone know how ?

Also, is there a way to return a field's original value if the argument
is false (without creating a circular reference) ?

Eg. If the original value in a field A5 is 100. I would like to use an
''IF'' function (in that same field) that changes the value if the
argument is TRUE but keeps the original value (100) if the argument is
false.

Thanks


Post your answer or comment

comments powered by Disqus
Problem-

I have hit the problem in Excel, that any function that references a blank
cell destroys the "blankness" of the result. You can convert Null (a blank
cell) to zero, or an empty string, but these are not the same as blank!

In the original source range, I can use a conditional format of "Cell value
is not 0" (bizarrely, but it works) to highlight cells that contain any
number or text. I can also create subtotal lines and use the SubTotal count
function SubTotal(3, [Range]) to count the nonblank cells.

However if I make a duplicate copy of the range on another sheet using any
function, the function result is always converted to zero (which screws up
the subtotal count) or an empty string (that screws up the conditional
formats).

I tried writing a custom "Keepblank()" function, but even here if the
function returns a null value Excel is converting this to a zero.

This has to be an FAQ. Is there a way with functions, or do I have to write
VBA code to bulk-copy the range to retain the blank values?

Problem-

I have hit the problem in Excel, that any function that references a blank
cell destroys the "blankness" of the result. You can convert Null (a blank
cell) to zero, or an empty string, but these are not the same as blank!

In the original source range, I can use a conditional format of "Cell value
is not 0" (bizarrely, but it works) to highlight cells that contain any
number or text. I can also create subtotal lines and use the SubTotal count
function SubTotal(3, [Range]) to count the nonblank cells.

However if I make a duplicate copy of the range on another sheet using any
function, the function result is always converted to zero (which screws up
the subtotal count) or an empty string (that screws up the conditional
formats).

I tried writing a custom "Keepblank()" function, but even here if the
function returns a null value Excel is converting this to a zero.

This has to be an FAQ. Is there a way with functions, or do I have to write
VBA code to bulk-copy the range to retain the blank values?

I want to return a set of records that were reported before a specified date
and have no value in the Finished column. I need to specify a null value in
a criteria range: For eaxmple ReportDate and ACTFINISH are my two headers and

Hi there,

I have a multiple data sheets with several thousand rows each and I need to do 2 things that I can't figure out a way to do.

I have a qualifier field (the one with "s" values in the attached image).

I need to bring over the other cell field values from the previous and subsequent rows that have an "s" (highlighted section).

The 2 complications are that:

1. I need to be able to drag a formula down the entire sheet without getting any values in the other rows [e.g. IF(cellX="s", yada yada, NULL)] so that I can copy and paste special excluding empty cells later on.

2. Shaded cells indicate a sampling boundary, so I need to exclude some data if an "s" occurs in a row with a shaded data value.

But I can't figure out any syntax that returns an empty cell from an IF statement, nor can I figure a way to use cell formatting as a criteria in an IF statement [e.g. IF(cellX=#shaded?, Y,Z)].

Can anyone tell me

A. Is there is a way to return a null value from a function?
B. Is it possible to use a cell's formatting as part of a function?

Thanks!

I have 2 files that have 2 data fields in common. I need to match both fields from one worksheet to another worksheet and return a 3rd value back to the other worksheet. Only with both data fields will I have one correct returned value. Here is an example set. I know I can use VLookup if I only need to match one value but I have to match two. Any help is appreciated. I feel like this should be easy but for some reason I just can't figure out tonight.

Thanks!

Worksheet 1 A B C D Code File # Name IUZ 123456 Joe Adams--This is the result field where I need to put the formual Worksheet 2 A B C D File # Code Name IUZ 345762 Billy Bob 4TZ 123456 Freddy Boy IUZ 123456 Joe Adams

Hi everybody.

I have successfully populated a validation list in Excel 2007 using a record from an Access 2007 table. However, now I need to include a single "blank"/"null" register on the list, but the method that I'm using to copy the records to Excel (the "transpose" function) doesn't allow null values in the matrix. Is there another way to include a null value on the list?

Here's part of the code:


	VB:
	
 
    dbvetor = Application.Transpose(Application.Transpose(rs.GetRows)) 'Copy the recordset values
    With Range("B2:B9").Validation 
        .Delete 
        .Add Type:=xlValidateList, Formula1:=Join(dbvetor, ",") 'Creates the validation list
        .IgnoreBlank = False 
    End With 
End If 

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

How do I create a formula so that if the value of a cell is between 0-50 it returns a certain value, if it is between 51-75 another value, 76 to 100 another value and so on? Thanks.

I am using an excel spreadsheet as an ADO data source. When I do a sum query sometimes the complete column is empty & that returns a null value not zero. Then when I try to do an operation on that sum (i.e. sum1 + sum2) it fails. Here is an example of my SELECT statement if it matters.

	VB:
	
SELECT sum(col1) + sum(col2) + sum(col3), sum(col4), sum(col5) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
If say col2 is completely empty then I get null, sum(col4),sum(col5). Even if col1 & col3 are full of data. Is there a way to force null values to be returned as zeros?

The null value being returned works quite nicely in other parts so I don't have all of these zeros everywhere, but when I do the summing I need a zero so the math operation doesn't fail.

Thanks, Chuck

I'm trying to copy a value from 1 worksheet to another. Worksheet 1 is a data worksheet where there might be days with no work and thus no data. Worksheet 2 is my calculation worksheet where I'll need to import data from various worksheet (incl wksheet1) to generate results.
e.g.
Worksheet 1
1/1/06 3:00
4/1/06 3:50
7/1/06 6:00
8/1/06 4:50

Worksheet 2
1/1/06 3:00
2/1/06 0:00
3/1/06 0:00
4/1/06 3:50

I tried using VLOOKUP to search worksheet 1 to return the corresponding value. but when a match cant be found, #NA was returned. Is there another function that will search the column and return a FALSE value or a specified value like 0:00 instead of #NA?

Can anyone help?

I have a list of numbers and 40 ranges. I want to find the range which the number falls into and return a corresponding value.

For example:

Number = 100200
Range A = 100000 - 100100
Range B = 100101 - 100200
Range C = 100201 - 100300

I want the value returned to be B.

Thanks.

Hi guys,

I've searched the FAQ but can't find what I'm after!

I have a list of eastings and northings for specific incidents, then in a seperate sheet I have a list of eastings and northings for postcodes.

What I want to end up with, is which postcode the incidents are in!

So it means getting Excel to find the closest match of two values to two other values and return a corresponding value.

Here's what it looks like!

Incident Easting Northing
01 437302 113607
02 437340 113673
03 437366 113701
04 437366 113701
05 437474 113581

Postcode Easting Northing
SP410TQ 430449 91321
SP410PY 429158 91339
SP410QB 429258 91375
SP410PZ 429288 91407
SP410EA 428940 91444

I want to end up with

Incident Postcode
01 SP7185H

etc etc.

Can anyone please help ASAP??

Many thanks

Chix

When I insert a double quote ("") into a cell as a Null value this causes problems when creating formulas that use the cell.

I've searched many posts to do with Null values but can only find information on checking for a Null value, like ISBLANK().

But, I've not been able to find any information on how to write a formula like
=IF(ISBLANK(A1), "",A1/B1)
and substitute the double quote ("") with a value that is Truly Null?

Sheet 1, cell A3 = Sheet 2, cell A3. Easy enough to put =Sheet2!A3, however if that cell is blank it returns a 0, and I don't want anything to show unless there is some sort of text or data in the cell.

I tried using an IF formula, =IF(Sheet2!A3="","",Sheet2!A3)

While the first part (Sheet2!A3= "") is True, I obviously I don't know how to tell it to return a null or blank. I know this is probably the simplest of questions, but I can't find it in a search.

Hello, i am a unprofessional Excel user. I use Excel from home to catalog my
professionally graded sports cards. I collect PSA graded cards that are
given a value between 1 - 10 depending on the quality of the card. 1 being
basically junk, and 10 being the best (Pristien, Gem.....).

PSA also offers qualifiers grades if you have a beautiful card that for
example was factory miscut.... a grade of 9Q will be given to a card that is
in Gem condition, but has one of a couple uncontrolable flaws. But the
problem is that a unqualified 9 is worth 9pts toward a set to where 9Q is
worth less.

I am using a drop menu assinging a value to each card in the individual set
between 1 and 10, and 2Q and 9Q.

My problem is that Excel trys to recalculate my formula using Q9 (cell
range) instead of 9Q in my drop down menu.

The formula i am trying to use is
=IF(C12=9Q,"7.77","1")
C12 obviously is where the drop menu has been selected to 9Q.
7.77 is what i want displayed in a seperate area for calculation of the set.
1 is what would be displayed if anything other than 9Q is selected from the
menu.
Excel returns this as a proposed correction
=IF(C12=Q9,"7.77","1")
Which does not work with the drop menu, it is looking at cell Q9 instead of
returning a value if 9Q.

It works just fine if i assign a different number value to the 8 possible
qualifier grades, but looks very messy.... I really want Q9, Q8 ect. to
display properly and still return a different value on my calculation portion
of the sheet (Looks much more professional)

Digging through the Excel website and help section in Excel has not helped
much on this issue :/

Am i missing a better simple function that i should use? Or is there
another formula i should be using? Help would be great! Thanks in advance

Thanks, Stozy

I have a large spreadsheet of information that I am trying to simplify by
creating a "group" column. I need to build a formula that will look at the
number value in another cell and return a letter value based upon where
number value falls in my predetermined range.

For example; The formula will be in cell B1. I want to look at the number
value in cell A1. If the value in A1 is 10-50, 80 or 81 I want "F" to appear
in B1. If the value in A1 is 60-69 I want "V" to appear in B1.

Hello,

I am having a problem trying to return a blank in a cell. I have a spreadsheet with formulas that are all working fine. What I am trying to do is return a blank value if the cells referenced in the formula are blank.

Here is what I have.

Cell A4 - Date of Req
Cell B4 - Date Req Rec
Cell C4 - Date Entered
Cell D4 - Hrs/Day
Cell E4 - Cycle Time

Cell A5 - 11/1/11
Cell B5 - 11/3/11
Cell C5 - 11/5/11
Cell D5 - 8
Cell E5 - 2

In Cell E5, the formula I am using is:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B5&":"&C5)),2)<6),--(ISNA(MATCH(ROW(INDIRECT(B5&":"&C5)),U5:U18,0))))

My question is if cells B5 and C5 are blank, how do I include it in the above formula? Cells U5-U18 represent a list of holidays.

Any help would be appreciated.

Thanks!

I am trying to simplify the values of two cells. What I want to do is return a new value based on only a partial value of a cell. Probably easier to show you.

In the example, I'd like to associate just the first part of the text string in the cell to return a specific value. I'm assuming I would need to setup a separate table of the text strings and what they associate to. But after that I get stumped.

thanks for any help!

Bryce

Hello everyone,
I have a cell which should return a text value (from column C in a sheet1) if in another cell a date falls between 2 dates (which ranges are in columns A and B in sheet1).
I included an example for easy explanation!

Thanks for your help!!!

R

I need a formula to return a blank value. Often it's good enough for it to return an empty string (""), but in this case, I need text in the cells to the left of the formula cell to extend over the formula cell, as it would if the cell were truly empty. How can I do this? Here's the code in each cell:
The attachment ExcelBlank1 shows what it actually looks like.

Attachment ExcelBlank2 shows what I want it to look like.

How can I achieve this?

Thanks - Rowan

Hey - hoping someone's come across this before, it's got me stumped!

When trying to grab a cell value from elsewhere in the same sheet, it's returning a 0 value with an error code about the cell not being protected. also occurs when trying to enter a sum formula within that cell, it sums to 0.

Have checked cell formatting, all are set to currency, and most are checked as 'protected' (but not sure what that has to do with the price of fish).

If anyone can offer some advice, it would be most appreciated!

Thanks.

Hi All-

First question posted here--I tried hard to search, and didn't find an answer, so I hope this hasn't already been answered.

I'm trying to create a mileage log for my girlfriend. She travels to 7 separate locations, which I will add to a drop-down list.

What I'd like to have is site 1 in (for example) A1. Site 2 would be in A2. I'd like B2 to return the mileage between the two.

Since its a minimal number of sites, the permutations of distances can be relatively easily calculated and listed off worksheet in a separate array if necessary.

But I guess my big question is, can I return a numerical value from a difference between two text cells?

Many Thanks.

I'm trying to create several charts from my excel data. Several of the
chart's reference cell (established range) contains formuals. When the
cell's formula returns a null value, chart display's as zero. How do I setup
chart to display null as nothing versus zero?

hi there,

I'm trying to find a formula which will return a text value from a data set I'm using...

to explain myself better - I have data along the lines of

Fields (columns):

Job Number, Employee name, Job title, Week commencing

Sample data:

xy1234, Pete, Director, 01/08/11

I want a formula that uses job number, job title and week commencing as the variables, and gives the Employee name as the result.

I've tried lots of combinations of IF, AND, INDEX, MATCH, VLOOKUP etc and they all seem to work only for the first instance in the list and then either give no result or repeat the first result.

It seems so obviously easy in my head, I just can't seem to get it running. Any help would be greatly appreciated!

many thanks
Ed

what is the formula if a cell in sheet2 returns a null value, the cell in sheet1 will not display a 0 but an empty cell...

how do i do it?


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