I am looking for a formula that will work with my current formula, and check that cells a1 and b1 contains information. If they do contain info then the original formula should carry on as usual and leave the answer in cell c1. I also need it so when no information has been entered in cells A1 and B1, C1 will be blank instead of containing a zero.

This is the formula I have at the moment:

=IF(SUM(A1=0,B1>0),B1-A1,"No Charge?")

A B C

1 0.18 1 0.82

This is the formula I have at the moment:

=IF(SUM(A1=0,B1>0),B1-A1,"No Charge?")

A B C

1 0.18 1 0.82

- Return Blank instead of Zero
- Show Blank instead of Zero
- Index Match: Show blank instead of #NA for no match
- Blank cell instead of zero
- Macro to make cells blank and shift next cells in the blank spaces
- How to Return a Blank instead of a 0
- How to show 0.00 instead of #DIV/0!
- Adjusting a formula to return Blank instead of a zero
- Can you use a formula to make a truly "blank" cell
- Can you use a formula to make a truly "blank" cell
- If statement to return blank instead of zero
- Help with formula to make cell return a blank (solved)
- Assistance on Ranking Formula to make blank values equal to zero
- How to make the cell pointer move to the first blank row of a table?
- IF function returning 0(blank) instead of TRUE.
- If function to return empty cell (blank cell) if false
- How do I use cell reference instead of name in a formula?
- Formula to count cells not blanK
- Help with formula to make cell blank
- Re: To Leave Cell blank Please
- Formula to count cells not blanK
- Identify active cell and use the column to add formula to another cell
- Return Blank instead of $0.00 in sumifs
- Adding 0 to the beginning of the cell, to make the total no of digits becomes 10

calculations from blank cells then I used =If (B2="","",B2-1), the problem is

that when I make a graphic with my new values, cero appears in the graphic.

"Frank Kabel" wrote:

> Hi

> =IF(B1="","",B1)

>

> "Db1712" wrote:

>

> >

> > I have two cells linked, is there a formatting to return a blank

> > instead of zero when the cell its linked to is empty

> >

> >

> > --

> > Db1712

> > ------------------------------------------------------------------------

> > Db1712's Profile: http://www.excelforum.com/member.php...o&userid=15912

> > View this thread: http://www.excelforum.com/showthread...hreadid=314201

> >

> >

I have 5 sheets. I have used the following formula to show Blank instead of Zero in Sheet2.

=IF('Sheet1'!A5+'Sheet1'!A7)="0","",('Sheet1'!A5+'Sheet1'!A7))

Where, in Sheet1, A5 is a value entered and A7=A5+A6

But with this formula I get the value '0.00' in Cell B3 in Sheet2.

Could you help me as to how to get Blank cell instead of Zero in Sheet2 in Cell B3

Thank you all for your kind help.

How can I modify the following formula to show a blank instead of #NA when no match is found?

=IF(LEN(VLOOKUP($A12,'FD RUN LIST - MASTER'!$A$12:$N$596,$B$17))=0,"",(VLOOKUP($A12,'FD RUN LIST - MASTER'!$A$12:$N$596,$B$17,FALSE)))

Is there another formula I should use so I get a blank instead of zero?

I have the following macro that make cells blank if for example AK3>=AL3.

VB:How do I change my macro to shift the next cells up if the cells were made blank? I have another problem in that my macro runs forever. How do I get it to stop? The macro can stop once it finds the first blank cell after a cell with data?del() Dim myRange As Range Set myRange = Range("AK3:AL3" & Range("AL" & Rows.Count).End(xlUp).Row) For i = 1 To myRange.Rows.Count If myRange(i, 1) >= myRange(i, 2) Then myRange(i, 1) = "" myRange(i, 2) = "" End If Next i End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

In the attached file, I have a formula to return the abs value of H8-G8.

I would like to not see the 0 when there is no data in H8 or G8.

Then I have a formula in K7 that I believe I have set up correctly?

I want it to show the Green check mark when the value of I7 is < 0.135,

and a Red X when the value of I7 is > 0.135, but I want it to show nothing

when there is not a value in cell I7.

Please help!

1 0 8 16

2 0 9 16

3 0 9 16

4 0 9 16

5 0 8 17

6 0 8 17

7 0 8 17

8 0 7 18

9 0 7 18

10 0 7 18

11 0 7 18

12 0 8 17

13 0 8 17

14 0 8 17

15 0 8 17

16 0 7 18

17 0 7 18

18 0 7 18

19 0 7 18

20 0 7 18

21 0 8 18

22 0 10 17

23 0 10 17

24 0 10 17

25 0 10 17

26 0 8 18

27 0 7 19

28 0 7 19

29 0 8 19

30 0 0 0

31 0 0 0

AVG. #DIV/0! 8.00 17.45

My formula is: =(SUM(B3:B33)/COUNTIF(B3:B33,">0")) How can I change the formula to show 0.00 instead of #DIV/)!? I must use the countif function because each month is a different number of days, the workbook doesn't belong to me so I can't shorten the rows.

I am using Macros to "delete a column and insert a new column by copying the same formulas(sum) and number formats".While doing this, the formula is calculating the sum of blank cells and returning zero(which i do not want to display).if i use the spaces instead of zero to display,it is erasing the formula.can u plz help me out with this.

When i used tools->options->view: unchecked the zeros values ...i cannot really enter a zero value for summing,as well the previous cell which contain zero is also removed.

A1: 20

B1: =if(A1Options

checked, so that blank values are not plotted as zeros.) However, it is

graphing my "not quite blank" cells, generated by the above formula, as

zeros. It does skip truly blank values (if I delete the formula altogether),

but doesn't when I try to force blank values using a formula like this.

Any ideas how to get a formula to make a cell think it is blank?

Thank you,

Heidi

A1: 20

B1: =if(A1<50,"",A1)

C1: =isblank(B1)

C1: produces "false". Why? Isn't "" supposed to put "nothing" into a cell?

The point of all this: I want the cell to be truly blank, so that it doesn't

graph as a "zero" in a chart. (Yes, I have the option under Tools>Options

checked, so that blank values are not plotted as zeros.) However, it is

graphing my "not quite blank" cells, generated by the above formula, as

zeros. It does skip truly blank values (if I delete the formula altogether),

but doesn't when I try to force blank values using a formula like this.

Any ideas how to get a formula to make a cell think it is blank?

Thank you,

Heidi

So I have =A1. If A1 is blank that returns a 0. I want it to return an blank. Is there a way to do that?

I want to make a graph where I have numbers and cells that are blank. Right now I have numbers and zeros so when my data stops the graphs jumps down to zero and stay there.

Hope that's clear, thanks!

This is what the table looks like showing weeks 1 to 4 set at zero's. Normally once the year has gone under way it will be populated with positive values.

Dept NPE 0 0 0 0 NPM 0 0 0 0 NPN 0 0 0 0 PE 0 0 0 0 PM 0 0 0 0 PN 0 0 0 0 PRODM 0 0 0 0 PRODN 0 0 0 0Immediatelly below it is a table that will show the departments in their respective positions for each week of the period.

1 NPE NPE NPE NPE 2 NPE NPE NPE NPE 3 NPE NPE NPE NPE 4 NPE NPE NPE NPE 5 NPE NPE NPE NPE 6 NPE NPE NPE NPE 7 NPE NPE NPE NPE 8 NPE NPE NPE NPEThis is the code that lies in each cell within the second column and reads from the table above;

But as you see it places the top department in all of the cells but ideally I would like it blank. I do have an annual table at the end of the book which reads from 13 periodical sheets but as you can imagine it is full of NPE! Is there a way out of my dilemma?

I have attached an example sheet.

Many thanks.

Duca

I've made a ranking formula that actually works, but I need to have the formula to make blank values to 0 (zero). I have the totals on J5:J33 and the ranking value on K5:K33. Any assistance will be appreciated. thanks. Here is my formula:

=IF((SUM(IF( $J$5:$J$33=J5,1))) =1,(SUM(IF( $J$5:$J$33>=J5,1,0))),( SUM(IF($J$5:$J$33 >=J5,1)))-(( SUM(IF( $J$5:$J$33=J5,1))) -1)*0.5)

For this macro to work I'm using relative references and before I activate the macro I place my cell in the first row of the first column. This way when I activate the macro the code reads that the row is filled and simply moves down to the first blank row.

Thanks in advance.

=IF(ISNA(VLOOKUP(B302,ACCOUNTS!$A$2:$K$300,4,FALSE)),"Not Found",(VLOOKUP(B302,ACCOUNTS!A2:K300,4,FALSE)))

The cell in question is blank, nothing in there. However, if I type something then that will appear.

Why can't I get it to say "N/A" instead of returning a blank, but it WILL show if something is there?

Thanks for any and all help! Let me know if clarification is needed.

=IF(ISNA(VLOOKUP(E2,Shrink!$A:$K,11,0)),"ID Not in list",LOOKUP(E2,CHOOSE({1,2},0,LOOKUP(2,1/((Shrink!$A$2:$A$1000=MANAGERS!E2)*(ISNUMBER(MATCH(Shrink!$K$2:$K$1000,{"C","V"},0)))),Shrink!$G$2:$G$1000))))

to create sub-totals based on the rows above.

The problem is that some of the cells are set up as named ranges for use

elsewhere, and some are not. As a result, the formulas for my sub-totals

contain both names and cell references. When I try to copy these formulas to

adjacent cells for other months, some formula components change (eg. C35

becomes D35) while others don't (eg. Jan_Sales stays as Jan_Sales).

This makes a complicated edit, overly prone to errors. It is also not

something I am comfortable turning over to someone else to use on an ongoing

basis.

I could create names for the un-named cells, but this would result in a HUGE

number of names to set up. Is there any way to use the cell reference for the

named cells in the formulas I am creating?

sorry i'm a beginner

Thanks

--

pmarques

------------------------------------------------------------------------

pmarques's Profile: http://www.excelforum.com/member.php...o&userid=25110

View this thread: http://www.excelforum.com/showthread...hreadid=392466

=IF((N20,VLOOKUP(A2'SHEET1'!$A$2:$C$288,3,FALSE)*1 0,"")

Once I enter a number in A2, all formulas work fine and I get the desired

result. However, when A2 is blank, #VALUE is displayed in T2 because of

formula in U2. I need T2 to be blank until a number is entered in A2. I'm

sure it is a fairly easy fix but I can't work it out. Can someone help with

this? Thanks!

Thankyou and yes I've taken on board your comment about large Worksheet

but in this case it is a small one

Thankyou.

"Biff" wrote:

> Hi!

>

> Sometimes it's more efficient to use conditional formatting to "hide"

> unwanted returns!

>

> Instead of using an array SUM(IF, use this:

>

> =IF(SUMPRODUCT(--('From Charters'!$A$1:$A$900="Roskill"),--('From

> Charters'!$B$1:$B$900="Period.1"),'From

> Charters'!$D$1:$D$900)=0,"",SUMPRODUCT(--('From

> Charters'!$A$1:$A$900="Roskill"),--('From

> Charters'!$B$1:$B$900="Period.1"),'From Charters'!$D$1:$D$900))

>

> If your wb is large with lots of these types of formulas consider this:

>

> This formula takes twice as long to calculate and uses relatively large

> arrays.

>

> Biff

>

> "Steved" <Steved@discussions.microsoft.com> wrote in message

> news:C860019C-3CC8-4C33-AC75-6DC39543A736@microsoft.com...

> > Hello from Steved

> >

> > If no Data the cell is returning a 0 value I need it in this case to be

> > blank please.

> >

> > SUM(IF('From Charters'!$A$1:$A$900="Roskill",IF('From

> > Charters'!$B$1:$B$900="Period.1",'From Charters'!$D$1:$D$900,0),0))

> >

> > Thankyou.

>

>

>

i need a formula to count cells of collum D, that aren't blank

sorry i'm a beginner

Thanks

sorry i'm a beginner

Thanks

I need to be able to plot the zeros if the user enters zeros but not plot the zero if the cells are blank.

What I was attempting to do is to use the worksheet change event to add the formulas to a cell so that the chart does not plot the value until something was added.

Hopefully that is not too confusing.

In my change event I need to know that a cell in the range (B5:S10) was changed and that if it was D7 (for example) that I need a formula enterd in D11 [=SUM(D5:D10)]. If it was I5 then the formula would have to go in I11 [=SUM(I5:I10)].

I am new to Excel VBA (Usually Access) so any help would be appreciated.

Thanks. Jonathan

I have created a drop down list with gas, groceries and travel as the categories.

column a is labeled expenses and formatted as currency.

column b is labeled category with the drop down list in each cell.

column d contains each item, gas, groceries and travel on separate rows.

column e will contain the total for each item in column d using "sumifs formula".

If there are no travel expenses, instead of returning $0.00 I want the cell to be blank.

I know how to do this in a simple sum function but sumifs are sumthin else.

I have tried using <> for not equal to and everything else I can think of and I usually get a VALUE error.

how do I get it to be blank instead of $0.00???

Windows XP and Excel 2007

12345 -> 0000012345

12 -> 0000000012

432567 -> 0000432567

9 -> 0000000009

4444444444 > 4444444444

The 1st column shows the original figures. I wish to add "0" to the beginning of the cell to make the total number of digit becomes 10, what would be the formula? Please advise.

Thank you in advance.

Regards/Mike