| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Same formula, different results depending on machine?
I used the following formula to define a named range for a chart...
=OFFSET(Sheet1!$C$4,1,0,ROWS(Sheet1!$C:$C)-COUNTIF(Sheet1!$C:$C,"")-4) On my home PC and both PCs in my staffroom (all Excel 2003) everything worked perfectly. However, on a laptop (Excel 2000), the range plotted by the chart was wrong and I kept getting a message about having more than 32000 points. Thinking it was a 2000 versus 2003 difference, I swapped to another laptop with Excel 2003, but the problem remained. I tracked the problem down to the COUNTIF(Sheet1!$C:$C,"") formula. On my home and staffroom PCs it counted cells that were blank and cells rendered blank ("") by an IF formula. I've had to change the IF formula so that cells I don't want in the named range are changed to "NO" instead of "" and the formula that defines the named range is now... =OFFSET(Sheet1!$C$4,1,0,COUNTIF(Sheet1!$C$5:$C$100 5,"<NO")-1) Does anybody know why COUNTIF(Sheet1!$C:$C,"") would produce different results on different machines? Ken Johnson |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Same formula, different results depending on machine?
Hi Ken,
Doesn't work in xl2002 either. However does work in xl2007. However, the answer to your problems will probably be solved if you use the following:- COUNTBLANK($C$C) Regards, OssieMac "Ken Johnson" wrote: I used the following formula to define a named range for a chart... =OFFSET(Sheet1!$C$4,1,0,ROWS(Sheet1!$C:$C)-COUNTIF(Sheet1!$C:$C,"")-4) On my home PC and both PCs in my staffroom (all Excel 2003) everything worked perfectly. However, on a laptop (Excel 2000), the range plotted by the chart was wrong and I kept getting a message about having more than 32000 points. Thinking it was a 2000 versus 2003 difference, I swapped to another laptop with Excel 2003, but the problem remained. I tracked the problem down to the COUNTIF(Sheet1!$C:$C,"") formula. On my home and staffroom PCs it counted cells that were blank and cells rendered blank ("") by an IF formula. I've had to change the IF formula so that cells I don't want in the named range are changed to "NO" instead of "" and the formula that defines the named range is now... =OFFSET(Sheet1!$C$4,1,0,COUNTIF(Sheet1!$C$5:$C$100 5,"<NO")-1) Does anybody know why COUNTIF(Sheet1!$C:$C,"") would produce different results on different machines? Ken Johnson |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Same formula, different results depending on machine?
On Aug 11, 4:16 pm, OssieMac
wrote: Hi Ken, Doesn't work in xl2002 either. However does work in xl2007. However, the answer to your problems will probably be solved if you use the following:- COUNTBLANK($C$C) Regards, OssieMac "Ken Johnson" wrote: I used the following formula to define a named range for a chart... =OFFSET(Sheet1!$C$4,1,0,ROWS(Sheet1!$C:$C)-COUNTIF(Sheet1!$C:$C,"")-4) On my home PC and both PCs in my staffroom (all Excel 2003) everything worked perfectly. However, on a laptop (Excel 2000), the range plotted by the chart was wrong and I kept getting a message about having more than 32000 points. Thinking it was a 2000 versus 2003 difference, I swapped to another laptop with Excel 2003, but the problem remained. I tracked the problem down to the COUNTIF(Sheet1!$C:$C,"") formula. On my home and staffroom PCs it counted cells that were blank and cells rendered blank ("") by an IF formula. I've had to change the IF formula so that cells I don't want in the named range are changed to "NO" instead of "" and the formula that defines the named range is now... =OFFSET(Sheet1!$C$4,1,0,COUNTIF(Sheet1!$C$5:$C$100 5,"<NO")-1) Does anybody know why COUNTIF(Sheet1!$C:$C,"") would produce different results on different machines? Ken Johnson Hi OssieMac, It's not so much a problem, I have changed the formula to get the desired result, I was just curious as to why the COUNTIF formula would give different results on different machines. To me, it just doesn't make sense. Ken Johnson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Same formula, different results depending on machine?
On Aug 11, 4:16 pm, OssieMac
wrote: Hi Ken, Doesn't work in xl2002 either. However does work in xl2007. However, the answer to your problems will probably be solved if you use the following:- COUNTBLANK($C$C) Regards, OssieMac "Ken Johnson" wrote: I used the following formula to define a named range for a chart... =OFFSET(Sheet1!$C$4,1,0,ROWS(Sheet1!$C:$C)-COUNTIF(Sheet1!$C:$C,"")-4) On my home PC and both PCs in my staffroom (all Excel 2003) everything worked perfectly. However, on a laptop (Excel 2000), the range plotted by the chart was wrong and I kept getting a message about having more than 32000 points. Thinking it was a 2000 versus 2003 difference, I swapped to another laptop with Excel 2003, but the problem remained. I tracked the problem down to the COUNTIF(Sheet1!$C:$C,"") formula. On my home and staffroom PCs it counted cells that were blank and cells rendered blank ("") by an IF formula. I've had to change the IF formula so that cells I don't want in the named range are changed to "NO" instead of "" and the formula that defines the named range is now... =OFFSET(Sheet1!$C$4,1,0,COUNTIF(Sheet1!$C$5:$C$100 5,"<NO")-1) Does anybody know why COUNTIF(Sheet1!$C:$C,"") would produce different results on different machines? Ken Johnson Hi OssieMac, I tried your suggestion and it works well. Thanks for that. Ken Johnson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Same formula, different results depending on machine?
Hi again Ken,
I thought later why not use COUNTA and count the cells that actually have data in them? Regards, OssieMac "Ken Johnson" wrote: On Aug 11, 4:16 pm, OssieMac wrote: Hi Ken, Doesn't work in xl2002 either. However does work in xl2007. However, the answer to your problems will probably be solved if you use the following:- COUNTBLANK($C$C) Regards, OssieMac "Ken Johnson" wrote: I used the following formula to define a named range for a chart... =OFFSET(Sheet1!$C$4,1,0,ROWS(Sheet1!$C:$C)-COUNTIF(Sheet1!$C:$C,"")-4) On my home PC and both PCs in my staffroom (all Excel 2003) everything worked perfectly. However, on a laptop (Excel 2000), the range plotted by the chart was wrong and I kept getting a message about having more than 32000 points. Thinking it was a 2000 versus 2003 difference, I swapped to another laptop with Excel 2003, but the problem remained. I tracked the problem down to the COUNTIF(Sheet1!$C:$C,"") formula. On my home and staffroom PCs it counted cells that were blank and cells rendered blank ("") by an IF formula. I've had to change the IF formula so that cells I don't want in the named range are changed to "NO" instead of "" and the formula that defines the named range is now... =OFFSET(Sheet1!$C$4,1,0,COUNTIF(Sheet1!$C$5:$C$100 5,"<NO")-1) Does anybody know why COUNTIF(Sheet1!$C:$C,"") would produce different results on different machines? Ken Johnson Hi OssieMac, I tried your suggestion and it works well. Thanks for that. Ken Johnson |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Same formula, different results depending on machine?
On Aug 11, 7:18 pm, OssieMac
wrote: Hi again Ken, I thought later why not use COUNTA and count the cells that actually have data in them? Regards, OssieMac "Ken Johnson" wrote: On Aug 11, 4:16 pm, OssieMac wrote: Hi Ken, Doesn't work in xl2002 either. However does work in xl2007. However, the answer to your problems will probably be solved if you use the following:- COUNTBLANK($C$C) Regards, OssieMac "Ken Johnson" wrote: I used the following formula to define a named range for a chart... =OFFSET(Sheet1!$C$4,1,0,ROWS(Sheet1!$C:$C)-COUNTIF(Sheet1!$C:$C,"")-4) On my home PC and both PCs in my staffroom (all Excel 2003) everything worked perfectly. However, on a laptop (Excel 2000), the range plotted by the chart was wrong and I kept getting a message about having more than 32000 points. Thinking it was a 2000 versus 2003 difference, I swapped to another laptop with Excel 2003, but the problem remained. I tracked the problem down to the COUNTIF(Sheet1!$C:$C,"") formula. On my home and staffroom PCs it counted cells that were blank and cells rendered blank ("") by an IF formula. I've had to change the IF formula so that cells I don't want in the named range are changed to "NO" instead of "" and the formula that defines the named range is now... =OFFSET(Sheet1!$C$4,1,0,COUNTIF(Sheet1!$C$5:$C$100 5,"<NO")-1) Does anybody know why COUNTIF(Sheet1!$C:$C,"") would produce different results on different machines? Ken Johnson Hi OssieMac, I tried your suggestion and it works well. Thanks for that. Ken Johnson Hi OssieMac, COUNTA unfortunately also counts blank cells that contain a formula even though that formula has rendered the cell blank by returning the "" value. Ken Johnson Ken Johnson |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Same formula, different results depending on machine?
O.K. Ken that's a good point.
Just a little more info that I have now found is that in xl2002 if you put COUNTIF(C:C,"") into a new worksheet then it comes up with zero. However, if you enter some data in some of the cells then it still returns zero but if you delete the data entered (even using Clear All), it then counts the number of cells that had the data deleted. I guess that this means that it is not reliable using COUNTIF() to count blank cells and that is probably the reason for having COUNTBLANK(). I also have xl2007 and COUNTIF(C:C,"") seems to works O.K. Regards, OssieMac "Ken Johnson" wrote: On Aug 11, 7:18 pm, OssieMac wrote: Hi again Ken, I thought later why not use COUNTA and count the cells that actually have data in them? Regards, OssieMac "Ken Johnson" wrote: On Aug 11, 4:16 pm, OssieMac wrote: Hi Ken, Doesn't work in xl2002 either. However does work in xl2007. However, the answer to your problems will probably be solved if you use the following:- COUNTBLANK($C$C) Regards, OssieMac "Ken Johnson" wrote: I used the following formula to define a named range for a chart... =OFFSET(Sheet1!$C$4,1,0,ROWS(Sheet1!$C:$C)-COUNTIF(Sheet1!$C:$C,"")-4) On my home PC and both PCs in my staffroom (all Excel 2003) everything worked perfectly. However, on a laptop (Excel 2000), the range plotted by the chart was wrong and I kept getting a message about having more than 32000 points. Thinking it was a 2000 versus 2003 difference, I swapped to another laptop with Excel 2003, but the problem remained. I tracked the problem down to the COUNTIF(Sheet1!$C:$C,"") formula. On my home and staffroom PCs it counted cells that were blank and cells rendered blank ("") by an IF formula. I've had to change the IF formula so that cells I don't want in the named range are changed to "NO" instead of "" and the formula that defines the named range is now... =OFFSET(Sheet1!$C$4,1,0,COUNTIF(Sheet1!$C$5:$C$100 5,"<NO")-1) Does anybody know why COUNTIF(Sheet1!$C:$C,"") would produce different results on different machines? Ken Johnson Hi OssieMac, I tried your suggestion and it works well. Thanks for that. Ken Johnson Hi OssieMac, COUNTA unfortunately also counts blank cells that contain a formula even though that formula has rendered the cell blank by returning the "" value. Ken Johnson Ken Johnson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to use HLOOKUP for different sheets depending on results from 2 cells | Excel Worksheet Functions | |||
| CONDITIONAL RESULTS DEPENDING ON CELL'S NAME (sumproduct) | Excel Discussion (Misc queries) | |||
| Want to show/hide cells depending on other cells results | Excel Discussion (Misc queries) | |||
| Reference & update destin. cells on 1st machine from source workbook on 2nd machine. | Excel Discussion (Misc queries) | |||
| Compare 2 Worksheets Create a 3rd depending on results | Excel Discussion (Misc queries) | |||
Hybrid Mode
