Hello,
I'm working with a fairly complicated spreadsheet (to me) that is growing beyond the capacity of my brain (not overly hard) and now I'm stuck. I'm no excel expert and most of the formula's I've made were with the help of the Google. I'm working in Excel 2010.
This is hard to explain, so if you have questions or I do a poor job, please ask for more info. I get daily exports of stats from an SQL database, which I import via a macro into this spreadsheet. The spreadsheet then the spreadsheet has multiple calculations to figure out percentages and averages etc.
The top 3 rows of my spreadsheet (technically rows 5, 6 and 7) calculate the average the last 5 cells inputted in the column, the last 5-10 cells and the last 10-15 cells. Below is an example of the last 5 cells/days worth of data in this column (I'll refer to this is formula A):
=AVERAGEIF((OFFSET(JB49,COUNT(JB49:JB999)-5,0,5,1)),">0")
JB49 is where my data for this column starts.
This formula works fine in most columns, except for the ones with percentages, as I've got an issue with the count function in the above formula. It's not counting all of my cells and I think I've figured out why.
Each cell in column JB uses the following formula and all cells are by default percentage cells ('ll refer to this as formula B):
=IF(JA43="","",IF(IY43="","",IF(ISERROR(JA43/IY43),"0.0%",JA43/IY43)))
Basically, it divides JA43/IY43, but if either number is blank it makes the target cell blank (this leaves the cell blank, if there's missing data (aka future dates worth of data)). In addition, if the formula tries to divide by zero and results in a DIV/0 error, it outputs 0.0% instead. This 0.0% is my problem and it happens whenever a staff member is away and gets 0's as they performed no work.
The count function in formula A see's the 0.0% outputted from formula B as text instead of a number. The count function in turn doesn't count the cell, and my averages for the last 5, 5-10, 10-15 days are then skewed.
I've reduced formula A to just the count function and confirmed that if I replace the 0.0% obtained by formula B with manually typing in 0.0%, the count function then works properly, return the correct count.
What can I do to correct formula B to have 0.0% represented as a number instead of text according to excel.
I'm working with a fairly complicated spreadsheet (to me) that is growing beyond the capacity of my brain (not overly hard) and now I'm stuck. I'm no excel expert and most of the formula's I've made were with the help of the Google. I'm working in Excel 2010.
This is hard to explain, so if you have questions or I do a poor job, please ask for more info. I get daily exports of stats from an SQL database, which I import via a macro into this spreadsheet. The spreadsheet then the spreadsheet has multiple calculations to figure out percentages and averages etc.
The top 3 rows of my spreadsheet (technically rows 5, 6 and 7) calculate the average the last 5 cells inputted in the column, the last 5-10 cells and the last 10-15 cells. Below is an example of the last 5 cells/days worth of data in this column (I'll refer to this is formula A):
=AVERAGEIF((OFFSET(JB49,COUNT(JB49:JB999)-5,0,5,1)),">0")
JB49 is where my data for this column starts.
This formula works fine in most columns, except for the ones with percentages, as I've got an issue with the count function in the above formula. It's not counting all of my cells and I think I've figured out why.
Each cell in column JB uses the following formula and all cells are by default percentage cells ('ll refer to this as formula B):
=IF(JA43="","",IF(IY43="","",IF(ISERROR(JA43/IY43),"0.0%",JA43/IY43)))
Basically, it divides JA43/IY43, but if either number is blank it makes the target cell blank (this leaves the cell blank, if there's missing data (aka future dates worth of data)). In addition, if the formula tries to divide by zero and results in a DIV/0 error, it outputs 0.0% instead. This 0.0% is my problem and it happens whenever a staff member is away and gets 0's as they performed no work.
The count function in formula A see's the 0.0% outputted from formula B as text instead of a number. The count function in turn doesn't count the cell, and my averages for the last 5, 5-10, 10-15 days are then skewed.
I've reduced formula A to just the count function and confirmed that if I replace the 0.0% obtained by formula B with manually typing in 0.0%, the count function then works properly, return the correct count.
What can I do to correct formula B to have 0.0% represented as a number instead of text according to excel.
