Free Microsoft Excel 2013 Quick Reference

Blank formula fields

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?


I have a somewhat similiar problem.
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

I am linking a MS Query through an ODBC connection to a worksheet. I am using Excel 2003.

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 ;-)

e.g. A1 - data entry, B1 - VLOOKUP function, C1 - VLOOKUP function, where B1 and C1 look up A1.

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!

How can I set up a blank form field (a fill in the blank field) in an excell
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).

How can I set up a blank form field (a fill in the blank field) in an excell
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).

Hi,

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

I would like to be able to access/edit the contents of the formula field without having to, heaven forbid, use the mouse to click on the field to edit specific characters. I don't want to re-type the content of the field, which currently is the only way I can find to avoid using the mouse. I am cleaning up an extremely large mailing database that needs a lot of editing, so I need to shave off all the seconds per entry I can.

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

In a data file I just received, many blank fields show a single-quotation
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?

In a data file I just received, many blank fields show a single-quotation
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?

=IF(G15:G21,"0",IF(I15:I21,"1","0"))

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.)

is there a formula that will insert a cost if there is a number in another cell but remain blank if there isn't. Example

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

I have one column of data that is 3000 cells large. Next to it I have another column with the same number of cells. I would like to copy only the cells with data (text or numbers) to the other column omitting all of the blanks. Here is an example of my two columns.

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 An Excel spreadsheet of contacts with COMPANY, NAME, ADDRESS, CITY, ST, ZIP, REGION, PHONE, E-MAIL fields.

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.

Hi. When I insert a field into a PivotTable formula it uses only the sum of
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

I have an excel sheet containing formula. If I change the field value/s
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

Hi All

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!

I have a formula...LEFT(D2,FIND(",",D2,1)-1)
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

I have a self defined formula field in a Pivot Table. I want to drag that
into the data area and select "STDEV" ...but all options are greyed, it
defaults to "SUM" ? How do I calculate StDev in this case ?

When you build custom formulas w/ VBA, how do you comment the input fields?

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!

I have an excel sheet containing formula. If I change the field value/s on which formula depend, It should recalculate value of formula field.
For this which option I should set? Or check Add In?

I have a column of vlookup formulas, and when a match is found the output is a corresponding number, when no match is found the box will remain blank. In the next column I would like to sort the results, I know I can manually copy the column and paste values, then sort the column, but since I have to re-import data columns I would like to automate this.

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

I have a column row of formula's that take data from another worksheet, when
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

Hi,

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

Dear Community,

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