I am basically making a sheet that counts attendance, everytime an X is placed in a particular cell another cell counts that x. So if there are 25 x’s in one row, another cell will automatically add those x’s to show 25. That isn’t my problem. My Problem is the cell that contains the countif formula that adds the x’s are all 0 if there is no data for it to add. I want to make it blank because right now I have a whole bunch of 0’s going down one column and can’t figure how to get rid of it.
Right now I am using: =COUNTIF(C20:X20,"x"). I normally would take the time to research and look through your forums however I do not have the time at the moment and was hoping for a fast answer.
I want to write a macro that uses shapes to hide zeros; I have seen this done before but don't really understand how to set it up for myself.
On my sheet, there is a list of funds (Cells A4 to A79), a mixture of which will make up any given portfolio. Cell A1 houses a drop down list of portfolios. When I change cell A1, the numbers are automatically updated, as they are pulled from other worksheets in the spreadsheet. I want to be able to have the funds with zeroes next to them be automatically hidden. I believe there is a way to do this using shapes.
I have a sheet with many different cells containing formulas. When the sheet has no data entered yet, every cell needs to be blank, including cells that have formulas for data.
I've realized that I can't use the "Show a zero in cells that have zero value" option because if a cell has data entered in it and the result is 0, I sometimes need to see that 0 in the cell. Some cells I need to see the 0 result and some cells I need to see a blank cell if the result is 0.
I just can't figure out how to edit the formulas so they do what I need.
cell P11:
=IF(J11="","",INT((J11-10)/2)) If no data is entered in cell J11, then cell P11 should be blank. But if data is entered in cell J11 and the result in cell P11 is 0, then cell P11 should be blank.
Is there a method of formatting specific cells to show zeros? Or indeed I could turn on show zeros in options and then hide the ones I don't want. Any tips / advice on how to selectively display (or hide) zeros appreciated.
I am running a formula in Cells B34 and C34, but when I have no values in these cells, I get a "0" in my total cell E34. Is there a way to make the cell E34 show nothing at all when there is nothing in both cell B34 and C34? Like an "and" statement? If there is nothing in cell B34 "AND" C34 make cell E34 empty?
I have a PivotTable linked to a Bar Chart. (see attached JPG for example) I do not want the 0% values to show in the chart at all but I do want to see all of the other percentages. I have been able to hide these values in the PivotTable itself by Conditional Formatting or custom number formats but they still show up in the chart!? how I can get the 0% values to be hidden or not show on the charts at all? I don't want to have to do this manually for every 0% that shows because the data changes daily dynamically. A VBA or Macro solution would be preferred.
I'm currently using a Excel 2007 btw... I'm a newbie when it comes to VBA programming... I have attached a sample file for reference. I need to automatically copy the contents of Sheet1 to Sheet2 and Sheet3 (meaning everything you edited on Sheet1 should be automatically edited on the 2 other sheets.) Here are the conditions:
- the items with zero (0) quantity from Sheet 1 should be hidden on to the 2 other sheets. but when you replaced an item with 0 qty to other values (any value greater than 0), it should appear on Sheets2 and Sheet3. (Not affecting the format of the table)
- items without price should be highlighted.
- items that have values that are linked to other items should be highlighted. (ex. if the value for item1 for qty is linked to the number of qty to item 5)
- for Sheet3, Column D to Column G should be hidden
I am linking multiple workbooks together - the problem I am facing is that the cells that do not have any data inside the source workbook result in a "0" on the destination workbook. Currently I am using the paste link option. Here is the purpose of my workbooks. the "core" document will have multiple worksheets each pulling its data from single worksheet workbooks. These workbooks will contain current project status and will fluxuate in length.
I have a spreadsheet for which I have to set up a formula to get the minimum value from a range of cells, but that range can include blank cells, errors (#DIV/0) and zeros, all of which I want to be ignored. I can work out how to ignore EITHER the zeros
(=MIN(IF(C10:G100,C10:G10)),
or the error cells
(=MIN(IF(ISNUMBER(C9:G9),C9:G9)),
How to exclude both. If I try to combine both of these exclusion criteria it doesn't work and I end up with the answer #DIV/0, which is one of the values I want it to ignore.
with the data in the attached sheet, I create several different pivot tables that need show the count of the information in the columns M:DU. My issue is that the data is sent to me from a third party and the columns contain zeros that cause the counts to inflate.
What I would like to be able to do is run a macro that will search out any zeros in M:DU and replace them with a blank cell.
Unfortunately the number of rows increases with every monthly reporting cycle so the macro would need to be able to accommodate for that.
I’ve created a formula for this statistic and I’m happy with the results. Because I’m working with formulas, my only problem is the unwanted zeros. How do I hide zeros that show up automatically (i.e. #3 [blank] and Nov 09-June 10)? I can hide the numbers, but if I enter a zero to one of my future statistics it will not appear and I don’t want that to happen. Is there a way to hide those automatic zeros without affecting my real zeros?
I want to take an average of only non-zero numbers in a range of cells containing a few zeros. I know there is a function to do this, but I haven't been able to find it. With the normal average() function, the denominator is too large as cells with 0 are included.
I am calculating ratios to show as 1:0 or 3:2 etc using this formula: =IFERROR(CONCATENATE((K2/GCD(K2,N2)),":",((N2/GCD(K2,N2)))),"Nil")... I have a lot of columns that have zeros in them and the formula i am using doesn't seem to work for those...
Example
A B 3 6 should equal: 1:2 works OK 2 0 should equal: 2:0 my current result: 1:0 3 0 should equal: 3:0 my current result: 1:0 0 1 should equal: 0:1 works OK 0 0 should equal: Nil works OK 4 1 should equal: 4:1 works OK
Anything with a zero in the first column comes up as 1:0
i have a mock up football issue here i want to enter scores in sheet 1 and they automatically fill sheet 2 etc i know i can just =and copy sheet 1 A5 and so so but that leaves zeros which will start allmy formulas with 1 point i need a formula that will leave sheet 2 3 etc blank untill any score is entered
What I would like to do is average only every other cell AND also not count zeros. So =average(B38,D38,F38,J38,L38) generally the idea not averaging H38 & N38 since they are both zeros.
Here is a question about the chart in excel. I have a sort of data where only part of the data is in numbers, and when drawing the chart, the whole range of data were selected. Then on the chart, there is a line drop to the x- array, anyone knows how to get rid of it? It is a line chart. Here's an example if i did not clearly describe the problem
Here's data X Y 4354.28 5354.75 6352 7343.82 8aa 9aa 10aa 11aa 12aa
and between 7 and 8 on x, the line will drop from 343.82 to 0, and i don't want this part, i want the chart to show to 7 and leave the 8 to 12 blank..
I have the following formula which works perfectly unless one of the cells has a value of zero;
=MIN(SUM(AD13),MIN(SUM(AK13)))
When AD13 = £60.00 and AK13 = £94.00 (or vice versa) the formula returns £60.00, which is correct. BUT when AK13 = £0.00 obviously the formula returns £0.00, but I would like the formula to still return £60.00.
I have 2 columns which contain numbers that have laeding zeros. example:
Column H has 002345 and Column I has 0678. I have tried H1&I1 but the leading zeros are stripped off. Is there a way to combine 2 cells AND preserve the leading zeros in both cells ?
I have managed to retain leading zeros in a cell by formatting the cell and selecting Number and Custom and adding the maximum number of zeros I want to have in front of the number in the cell i.e. 000002.
I want to have another cell in the spread sheet which contains the value 35394000002 using the formula =A24&B24.
When I use this formula I get 353942. How can I retain these zeros?
I have 4 columns (and a couple of other text columns) that are formatted as decimal with 6 dicimal points.
I need to create a text file with lines that are 80 bytes each from the spreadsheet - no spaces inbetween each of the characters.
The issue I am having is trying to format the 4 decimal point columns to be fixed numbers and retain the leading or trailing zeros to do a concatenation with the other columns.
Just an example of some what some of the values look like on my spreadsheet:
.123456 | .123450 | .012345 | .012340
Essentially when I'm all said and done, I would like the row to look like "text123456123450012345012340text"
I tried to format 4 new columns as general and use a "=right(cell,6)" and that worked pretty well for the cells that have leading zeros, but for the cells that have trailing zeros, it doesn't seem to pick up the zero. I tried to use a "=mid(cell,2,6)" but that didn't work either.
Averages Monday=0 Tuesday=5 Wednesday=7.5 Thursday=0 (I have got a value return of 7.5) Friday=5 Saturday=0 (I have got a value return of 5) Sunday=0 (I have got a value return of 5)
I need to work out averages for cells higher than zeros, in other words, I need to ignore those.
Also say I have got an average of 5 by Tuesday and no number yet for the rest of the week, I still get an average of 5 for all days left which I do not want.
I am using excel 2003 and formula =SUM(RANGE)/COUNTIF(RANGE,">=0").
I would also like to hide the annoying #DIV/0! error.
I have a summary worksheet consisting of columns that are referenced to other sheets within the same workbook.
If the cell in the 'referring' (original) sheet is blank then the forumula returns a zero instead of a blank in my summary worksheet, which is messing up some other calculations.
Is there a way to make these cells truly blank, or possibly into string variables instead, so that they do not interfere with my calculations?
