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

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

- Can a function return a Null (blank ) value? Maybe a custom functi
- Can a function return a Null (blank ) value? Maybe a custom functi
- Specify a null value in an Excel Database criteria range
- Returning null values/cell formatting as criteria?
- Match 2 values in one row and return a 3rd value from one worksheet to another
- Populate a validation list including a "null" value
- Return a specific value based on other cell value being within a number range
- Ado Sql Null Values
- Return a FALSE value if lookup cant find a match
- Find the range that a value falls into and return a corresponding value.
- Matching two values to two other values and returning a 3rd value
- How to Insert a NULL Value?
- IF (probably simple) question to return a null
- Having trouble with returning a certian value using =IF function
- Return a given value if the value of a cell is in a given range
- How to return a blank value using my current formula
- Compare Partial Content of a cell then return a different value
- SOLVED: Multiple opts. How returning a text value in a cell if date falls between 2 dates
- How do I make a formula return a blank value?
- Formula & =[cell] returns a zero value...
- Formula to return a numerical value from a text difference between 2 cells?
- How do you stop excel from charting empty cells/null values as zer
- If statement to return a text value
- Return Value = null

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

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

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!

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

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:Thanks in advance.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 IfIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB: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?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

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

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?

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.

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

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?

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.

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

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.

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!

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

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

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

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.

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.

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?

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

how do i do it?

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