I have the below IF statement in e.g. F1 where F1 should display the answer of B1 * E1.

=IF((B21*E21)>0,"","")

Now what I want is if the answer of B1 * E1 is greater than zero I want the answer to show. If the answer is zero I want the cell, in this case F1, to be blank.

Possible?

I can get the blank to show but then I can't get the answer to show.

Any ideas?

=IF((B21*E21)>0,"","")

Now what I want is if the answer of B1 * E1 is greater than zero I want the answer to show. If the answer is zero I want the cell, in this case F1, to be blank.

Possible?

I can get the blank to show but then I can't get the answer to show.

Any ideas?

- Similiar to Blank Formula Fields
- Auto Fill Formula Fields For Data Driven Worksheet
- Similar once again to blank formula fields
- Mailmerge blank form field
- Mailmerge blank form field
- Insert a new row of blank formulas after clicking in current blank row
- Keyboard Shortcuts - Access formula field with content?
- Blank data field shows quote in formula, how to delete it?
- Blank data field shows quote in formula, how to delete it?
- IF formula and date fields
- 'if blank' formula
- No Blanks Formula Error - I can't seem to make it work
- Automate field entry (REGION)
- Can I create PivotTable formula using count of field not sum?
- Recalculate Formula when field value changes
- How to return a blank formula cell if the reference is blank?
- Value of formula field
- STDEV in a Formula for Calculated Field in Pivot Table
- Commenting custom formula fields/formula on formula editor
- Recalculate Formula when field value changes
- Sorting formula results
- Ignoring Blank/Empty Cells that contain formulas
- Validation List ignore blanks (formula)
- Leave empty if BLANK formula

I have this formula in N12 =IF(J12=K12,1,0)

If J12 and K12 are equal AND have numbers in them, all is fine. But if J12 and K12 are blank, it still returns the value 1.

What I want is, if J12 and K12 are blank ... to return the value 0 ... or ... nothing.

Would appreciate the help.

Excel Newbie

My Data contains Names and Addresses in which I need to use formulas to place the information into a usable format to me then Mail Merged into a word document.

The following formulas are being used to format names and addresses, and I would like to have the formula fields auto fill, so I do not need to copy them down the column each time the data is refreshed.

Names are in the format: LASTNAME/FIRSTNAME MI, the following formula is

=PROPER(MID(B419,FIND("/",B419)+1,100)&" "&LEFT(B419,FIND("/",B419)-1))

Address lines including City are simple Proper formulas:

=PROPER(D2)

Zip Codes are are in the format of 123451234:

=IF(LEN(TRIM(K2))>5, LEFT(K2,5)&"-"&RIGHT(K2,4), K2)

This format varies from sheet to sheet, but the ultimate goal is to have the sheet auto-fill the formula in each line so I do not have to drag them to the cells below each time I preform a refresh as there are multiple columns.

I have seen a couple examples here for columns to the right, but none for data below record style. My level of expertise with Excel is limited so be kind ;-)

When A1 is blank, B1 and C1 display #VALUE (or something to that effect). When A1 is blank I want them to be blank.

I put in an IF statement - IF(,(VLOOKUP(A1,'Table!A9:B20',2,0),"")

What do I put in as the first variable of the IF statement. Or are there some other ideas?

Please help!

spreadsheet that correlates with the fill in the blank field in my document

template and then mailmerge. I have the fields set up in my templete only

and they do not mailmerge with the other data into a new document(s).

spreadsheet that correlates with the fill in the blank field in my document

template and then mailmerge. I have the fields set up in my templete only

and they do not mailmerge with the other data into a new document(s).

I'm just wanting to insert a new row of blank formulas when I click in

(or change or whatever) the current blank row.

Column A is a list box based on a different work sheet, and columns B&C

are Vlookups also referencing that different work sheet.

Have tried a couple of modules in VBA, but I end up with a loop which

keeps adding or does vague inserting of random data!

Any help would be appreciated.

Cheers

Searches of shortcut lists have been in vain. Does anyone know of a command like a browser's alt-d function, or should I just try recording a macro? (And would that even work?)

mark in the formula. The statement does not recognize these fields as

blank. I have tried to 'find and replace' the marks using a search for > both without success. How can I fix these fields, in

bulk, so they behave like blanks?

mark in the formula. The <<if>> statement does not recognize these fields as

blank. I have tried to 'find and replace' the marks using a search for <<'>>

and also for <<~'>> both without success. How can I fix these fields, in

bulk, so they behave like blanks?

Where column G and column I are date fields.

This forumla keeps returning a #VALUE! error because the date field is not a

number. How do I write a formula to see blank date fields?

I want to count the number of rows where there is a date in row G and no

date in row I.

The purpose of the row is to indicate which sales stage the contact company

is in. The dates track entry into the sales stage. If column G has a date,

and there is no date in the I column then the contact is still in the Column

G stage. If there is a date in the I column that date indicates when it left

the Column G stage. I want the formula to count the G Column to indicate how

many accounts are in the Column G stage.

(Not relevant to this question, I can figure out how to count the days in

stage.)

if nothing is inserted into the 'quantity' box (I14)I don't want anything to show up in the 'cost' box(j14), however if a quantity is determined i need the cost to appear.

very simple question I'm sure, but couldn't find anything in the other posts.

thanks

DA DB

1 abc abc

2 def def

3 ghi

4 ghi jkl

5

6 jkl

I have column DA and would like to the result to appear as column DB. Here is a sample of a formula I found online and have tried, but can't get to work:

=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-

COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL(

(IF(BlanksRange"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),

ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))

Any ideas? Thanks in advance.

LC

We have internally divided up the US into seven regions. I would like to know how to update the “REGION” field automatically, as the respective “ST” fields are entered. For example; as IL is typed in, the corresponding REGION cell fills with “Great Lakes”, CT corresponds to “Mid Atlantic”, CA with “Pacific” – so on and so forth with all 52 states.

As the spreadsheet becomes bigger, what would be the quickest way to weed out duplicates? I would assume that a single query on the “ADDRESS” field would suffice, assuming the file is properly sorted (by ST, then ZIP, then ADDRESS).

(Hopefully this would help some of the "duplicate entry" questions). What we do now is to sort as above, then add a field next to ADDRESS with this formula in the first cell; =IF(X1=X2,1," ") with X being the corresponding cells in question. This formula is then copied all the way down. Wherever a duplicate address cell is found, the number "1" appears, else the cell is blank. We then create another blank field next to this "formula" field, copy and "paste special - values" the formula field to this new field, then sort the entire spreadsheet by this newest field, which lumps all the 1s on top. Then manually delete those rows.

Don't know if that was understandable, or perhaps even too amateurish.

Any pointers would be greatly appreciated, including how to manage this spreadsheet more effectively.

Thank you very, very much.

that field. I need it to use the count of that field - but not the

worksheet-version of COUNT, but the PivotTable version. I wish to my formula

field to display the same result as if I had set the original field's

settings to "summarised by: count" in the 'Field settings' menu option. I can

do further calculations once I can do this. Cheers C

on which formula depend, It should recalculate value of formula field.

For this which option I should set? Or check Add In?

--

patilprt

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

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

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

Let's say I have a formula in cell c1. It references a number input into

cell a1, and does some math with that cell and a fixed number in cell b1.

Now what I want to do is have cell c1 be blank if cell a1 is also blank. But

when a number is entered into cell a1, I want the resultant formula answer to

be displayed in cell c1.

What do I add to my formula to do so, and where do I put it?

Thanks - y'all are a big help!

Basically it takes Civic Memorial Field, Somwhere, NV

and gives me only Civic Memorial Field.

However when I use this field in a mail merge using word it only recognizes

the number of spaces.

Is there anyway to add a funcion on my formula to return the actual value.

If I do a copy and paste special value it works correctly in the mail merge

but w/ this method everytime I change the document I would have to recopy and

paste special again. Hopefully I haven't confused anyone too much. Thanks

in advance,

Kreiss

into the data area and select "STDEV" ...but all options are greyed, it

defaults to "SUM" ? How do I calculate StDev in this case ?

Example: If you click on the edit formula button "=", then click on the

functions drop down box and let's say you select "sum" if you click on

the "Number1" field at the bottom of the formula editor shows a brief

explanation of the data to be typed and a description of the formula

itself. How does one do the same thing with code.

Regards!

For this which option I should set? Or check Add In?

Is there a way to take the output value from a formula field and then have them sorted in the following column?

there is an error in the original data it leaves the cell blank.

Formula =IF(ISERR('JAN 2005'!Y55),"",'JAN 2005'!Y55)

My problem is that there is a graph generated from this column and it treats

all the blank cells as zero, this messes up the graph and shows it dropping

to zero. (The errors alway happen at the end of the data)

Is there a way to get the graph to ignore the zero figures or rebuild the

formula to show the cell as truly blank?

Regards

Paul

I want to create a validation list with value comming from formula and I got blanks line. I have to create a dynamic list, I can have 15 possibility and it came from a lookup formula. When I choose the 15 cell with the lookup formula it show the empty cell.

I did a exemple of my problem. You can check the attachement to understand my problem in the cell C1.

Best regards

Good Afternoon from sunny but frosty kent, (already got through half a bag of coal today)!!

Please see formulae below:

I would like to add in this case below (if L2 is blank leave blank)

=INT((L2-I2)/30)

and in this case (if J3 is Blank leave blank)

=12*(YEAR(J3)-YEAR(I3))+MONTH(J3)-MONTH(I3)

I have been trying along the lines of =if"","",

Do I need an IF OR or IF AND S'ment to solve this?

Thanks All

Darren