I am using ISERROR to place blanks in my cells "" where it would normally find a #NA. But then when I do a COUNTIF to check if there is any data in the cell, it is counting the blank.
Is there any workaround for this?
After years of using Excel I have discovered today that Excel considers a numeric zero to be the same as an empty cell (at least in conditional formats).
The background to this, in case it helps, is that I have a series of expected results from a test run (in column E) and these are compared to a set of actual results loaded in from a SQL Server database (in column H).
My conditional format compares two cells using a formula like this ...
Code:
=IF(AND(H50<>E50,$B$2="Y",E50<>"*"),1,0)
H50 and E50 are the cells being compared.
$B$2 is a flag to determine whether or not SHOW the difference (using a pattern of red)
The "*" test is there to allow certain values to be ignored as they are known to always be different, timestamps for example.
What I am seeing is a numeric zero in E50 being compared to a NULL (empty cell) in H50 and being considered (by Excel) as identical.
I COULD alter the underlying data to make all cells text but this would be difficult to achieve across the project (hundreds of workbooks).
Can anyone help with a method of getting this compare to work "properly"?
Thanks in advance.
This is a repost. Gurus, please help. I need a formula that will allow
me to exclude cells that are zero, zero by calculation or blank.
Especially when a cell becomes zero (fromatted to show - (dash) for
zero)by a formula non of my trials worked. Please help
--
Ash
Good morning!
I apologize if this a recurring question...
I am trying to keep Excel from plotting zeroes for a cell that contains a formula, the result of which is a blank cell. Currently, it plots it as zero (as im sure you know). How can I get around this?
Thank you in advance for your help...sorry if it's a "noob" question...
Paul
I have data that I need to graph that contains Zero values. I have read previously if you create a formula that converts this Zero into #N/A by using NA() the graph will not represent this as an #N/A or a Zero. For some reason this is not working for me. Below would be an exampe of my data. Can somebody please assist?
Column A B C D
Row 1 Metrics Jan Feb Mar
Row2 # of Employees 12 11 #N/A
Hi,
I am trying to write a macro to automatically sort data in the range A2:A7 and place the sorted list in Colum B. The data in A2:A7 should not change. Only the sorted column B should automatically change when the user inputs data in A2:A7. Also, in the sorted column, i would like to ignore blanks and zeros. Once I have this sorted list I would like the sorted items to appear in the combobox. I have attached the spreadsheet for this.
Can anybody help me with this? or at least provide me some guidance.
Thanks,
~Jig
How do I hide rows containing zeroes or blanks in pivot tables?
Hello,
Lets assume :
Cells A1:A9 have the value 10
Cell A10 has the the value '0' (zero)
I wish to use the average function to get an average of A1:A10.
How can I exclude the ZERO?
AVERAGEA can exclude BLANKS but not zero.
Can anyone help?
Regards
Hiya,
I've been trying to get an averaging forumula to work with logical arguments.
I have all my data in a sheet called "Data", with Column C containing either "Occupied" or "Unoccupied"
Column A in the same sheet is the season "Summer", "Winter", etc
And Column F in the same sheet has the time of day.
The data I want to Average is in Column G.
On another sheet I have the hours of the day (midnight to 11:30pm) in Column A. In Column B of the same sheet I want it to average all the values in G on the Data sheet, with the matching time in Column A, if the variables in Columns A and C are "Summer" and "Occupied".
So, basically I'll have the average daily profile for days within the same season, and with the same occupancy condition.
This is what I've been trying to make work, and Excel keeps removing the ' marks around the references to the Data sheet and pinging back a blank cell with an error:
VB:
=If(And( 'Data'!$C$3:$C$35138="Occupied", 'Data'!$A$3:$A$35138="SUMMER", 'Data'!F15=A15), AVERAGE('Data'!$G$3:$G$35138), "")
If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines
Can someone help?
Thanks!
Hi,
I have been grappling with this question for some time now wondering if anyone can help...
I am attempting to calculate an average percentage whos range will be moving one cell up every month upon update.
If you look at the attached spreadsheet, columns A and B start counting up from 0 as soon as a valid month is entered. (formula for this excluded)
I am using columns A and B, and row 3, in a SumProduct function in order to get the numerator of a calculation that will arrive at the average.
The sumproduct function is entered in rows 43 and 44.
The function is as follows for cell F43:
=(SUMPRODUCT(($A6:$A39<M3)*($B6:$B39>=G3)*(F6:F39)))/
MIN((M3-G3),(COUNTIF(F6:F39,">0")))
The numerator of this formula uses the adjusting numbers in columns A and B to always arrive at the sum of the last 6 months of data points. So for column F, I am receiving in the numerator the sum for data points for F12:F17, which is the range between the numbers 3 and 8.
My issue revolves around the denominator. To get an accurate average, I need to divide the numerator by 4. I am instead dividing it by 6. (MIN((M3-G3),(COUNTIF(F6:F39,">0"))). = MIN(6,10) (using Min and CountIf for a later issue where there are less than 6 datapoints available)
I am restricted to leaving the zeroes in the blank fields below the data points, as I cannot have a non-numeric field in a sumproduct range. (I have tried replacing the "0"'s with ""'s and " "'s in the formula that results in "0"'s being placed where there is a null)
So for cell F43 the average states 20.10%, what I need is the average to be 30.15% which is the last 6 data points, exluding the 2 zeroes in July and August...
Any suggestions on this would be great!
