I would appreciate it a lot if someone could explain to me how I hide the value in the cell which a result is stored in when one of the two cells in the calculation is empty.
For exaple:
C1 should be blank if A1 or B1 is empty in this formula:
=A1+B1
I'm just stuck on a final thing......I would like the pivot table to collapse based on the outcome of a formula. For instance I've got a project that has A, B, C, D as component parts, these parts all have a percentage work completed. If let's say A is a 100% complete I would like to automatically collapse A, so it will just show the summary for A. Obviously this can be done by hand, but since there is quite a lot of data I would like to automate the proces.
On the attached worksheet if the data in col F changes from FLT to AWF,col M will flag up "faw".I would like to leave a blank cell instead of false if the condition is not met. Col P is an example of the result im trying to achieve.
I've written a sumproduct formula, which does what I want it to do; but it returns a zero in false condition, instead of a blank. I've tried several things, but don't seem to be progressing very far. Here's the formula that I ended up with.
I am using the following formula and getting Div# - but I would like to put something in the formula that says if it pulls Div#, instead show blank - does anyone know how to do this?
I know you can use IS error with V lookups & LEN - but not quite sure with this.
Sheet3 Â ABCD1Â Â Â Â 274917654Â 7654374927635Â Â 474917632Â Â 574327524Â Â 675247492Â Â 775247491Â Â 874917432Â Â 976320Â Â 1076350Â Â 1176540Â Â 1274910Â Â 1374920Â Â 1474910Â Â 1574320Â Â 1675240Â Â 1775240Â Â 1874910Â Â 1976320Â Â 2076350Â Â Spreadsheet FormulasCellFormulaB2 =MAX(A2:A31)B3{ =MAX(IF($A$2:$A$31<B2,$A$2:$A$31))}B4{ =MAX(IF($A$2:$A$31<B3,$A$2:$A$31))}B5{
[Code]...
Formula Array:Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!
I want to ask that how can i remove zero from data validation list OR from column B...
I have a page that shows staffing levels. If the member of staff is in work then I have to enter a value of 1 on each member so I can calculate the number of staff present. If they are on leave or similar then I enter a text value such as AL which discounts then from the total staff.
Is there a way to have the default value of 1 in a range of cells if the cell is blank automatically? Can this be done via VB?
I attach a file for you to more easily understand my problem. I have a big table with information, then I would like to summarize this, if all fields for certain "Type" (see file) is blank, then I would like to use another value that I keep as a separate row in the summarize table. But I also need to have it to work if zeros are put in, I need to show that value, that's my biggest problem right now when using sumif forumla.
This is my formula. =IF(SUM(P5:P9)=0,"",SUM(P5:P9)
When P5 & P6 = 0 and P7 through P9 are blank, nothing populates for the answer. How do I make the answer = 0, when some 0,s are present in the equation?
I am trying to show a blank cell if the others don't have any figures in there and am using the following formula.
However, in my cell it is showing "#value" instead. How do I get my cell to look "blank" when there are no values in the other cells? Here is my formula
Please see attached workbook. I know for a fact this isn't the most effective way to do this, but I just needed something really quick for a small worksheet that my department at work is using. A1:C7 are supposed to represent 3 different types of "methods" In the case of my worksheet, I just typed random stuff.
Basically, I have data validation in B10. Depending on which one I select (1 corresponds with A1:A7, 2 with B1:B7, and 3 with C1:C7), it is supposed to populate that data. I've done this with nested if statements in D10:D16. The issue is that for options 2 and 3, it shows 0's where the blanks should be.
I have a spreadsheet which imports data from worksheets week 1, 2 etc to a monthly summary. Is there any way I can show a cell as blank if the return equals 0?
I'm sure the resolution is probably simple but then so am I.
On my spreadsheet i have 4 columns which are in currency format. The 5th column (total) adds the 4 up which currently looks like this - =SUM(BG44:BG45) this shows £0.00 in the 5th column.
What i need is a formula in the 5th column (total) so that when it adds up the 4 previous columns if the sum = £0.00 then the total column should show a blank cell.
there is data going to excel from database. The data is something like jan to dec sales and in a arbitrary fashion. now if there wont be data availble for say month of july then nothing will be there.
Now i need to nicely formulate data from jan feb ..Dec and in same order in another cells. Now for empty cells data after formualting it is coming as #N/A. and by this i am getting a same thing in the application where this excel sheet is being used. So for eliminating it i need to use 'if' such that if it is undefined or NULL then blank should be there in the formulated cell.
I have three formula in a spreadsheet, all of which should return a number (which they do) or show blank. However, two of the formula return 'FALSE'. The formula are as follows:
I have a workbook containing several worksheets. I use one worksheet to collate information from the others. I do this by referencing the relevant cells I need from the other worksheets with the '=' command.
When this displays it shows as a '0' if the original cell is blank. Is it possible for this to show as a blank unless there is any data. I have tried the ISERROR function but it still leaves the entry as a '0'.
See attached spreadsheet for example of the sheet I am using.
Problem 1.
Columns C and D show me figures when the number in col A is > 2500 or < 550 when these criteria are not met a 0 is displayed in the cell, is it possible to alter my formula so that nothing (a blank cell) is displayed rather than a 0 - I am wondering if there is something like null which I can use?
=IF(A4>2500,A4,) =IF(A4<550,A4,)
Problem 2.
In column E (normal days) I want to display the value of A if it is > 550 but < 2500 - is there a way of doing this in a single formula? Again I would just like a blank to be displayed if the value of A does not meet the above criteria.