In need of a solution here...
I have several sheets in a workbook containing different sets of data. I then have one master sheet which references specific cells from all of these data sheets so only the info I need is displayed in an organized manner on one sheet.
The first problem I ran into was that when I referenced the cells from other sheets, if the cell is blank, the reference would display a 0, instead of blank. So to fix this I did an IF statement, where if the cell was "" then it would display "". This fixed the problem until I started charting the condensed data on the one master sheet (which references the other sheets using the IF statement). When I plotted this data, the empty strings "" would be plotted as a value of 0, instead of being left off the plot. There are option to plot "gaps" as zero, or as nothing, and I told it to plot it as nothing... only problem is that it doesn't see it as a gap, it sees it as "" which then plots as 0.. making my line graphs fall to zero, when it is far more accurate in my case to have these data points not displayed.
So I changed the IF statements to place #N/A in the cells instead of "", which fixed the problem for the charts.
NOW I am trying to take averages and perform statistics functions on this set of data and I am having some issues, as taking the average of a range containing #N/A cells will simply yield #N/A. I was able to use DAverage(), DMin(), DMax() and DStDev() with a criteria of <>#N/A to fix the problem, but I am also in need of quartile or percentile data. Unfortunately there is no DQuartile function (that I am aware of).
If there any kind of DQuartie or QuartileIF statement that I am unaware of?? Any suggestions on how I could do this? Thanks.
I have several sheets in a workbook containing different sets of data. I then have one master sheet which references specific cells from all of these data sheets so only the info I need is displayed in an organized manner on one sheet.
The first problem I ran into was that when I referenced the cells from other sheets, if the cell is blank, the reference would display a 0, instead of blank. So to fix this I did an IF statement, where if the cell was "" then it would display "". This fixed the problem until I started charting the condensed data on the one master sheet (which references the other sheets using the IF statement). When I plotted this data, the empty strings "" would be plotted as a value of 0, instead of being left off the plot. There are option to plot "gaps" as zero, or as nothing, and I told it to plot it as nothing... only problem is that it doesn't see it as a gap, it sees it as "" which then plots as 0.. making my line graphs fall to zero, when it is far more accurate in my case to have these data points not displayed.
So I changed the IF statements to place #N/A in the cells instead of "", which fixed the problem for the charts.
NOW I am trying to take averages and perform statistics functions on this set of data and I am having some issues, as taking the average of a range containing #N/A cells will simply yield #N/A. I was able to use DAverage(), DMin(), DMax() and DStDev() with a criteria of <>#N/A to fix the problem, but I am also in need of quartile or percentile data. Unfortunately there is no DQuartile function (that I am aware of).
If there any kind of DQuartie or QuartileIF statement that I am unaware of?? Any suggestions on how I could do this? Thanks.
