#1
|
|||
|
|||
Need help with #VALUE! errors in worksheet
Hello, Thank you all for your help with my previous problem. I have managed to resolve the issue somewhat. The formula I'm using is giving errors and I need some help to resolve. I have attached a copy of the xls. Thank you B |
#2
|
||||
|
||||
When I run evaluate on some of the cells having #value, the error that is occurring is that you are trying to add values to cells that are blank. You might be better off converting the blank cells to zero and see if that works for you. To test: Highlight the cell in question. On the Formula Tab, click on Formula Auditing and Evaluate. It will show where the errors are occurring at you tab through the formula
|
#3
|
|||
|
|||
The main problem was, that source cells were not empty/blank, at least some of them contained spaces.
For e.g. column BH the working formula (without editing all cells containing only space strings) will be Code:
=IF(COUNT(I4:Q4)>=5, (SUM(I4,J4,L4,N4,O4)+(IF(ISBLANK(K4),0,(5-SUM(K4))))+(IF(ISBLANK(M4),0,(5-SUM(M4))))+(IF(ISBLANK(P4),0,(5-SUM(P4))))+(IF(ISBLANK(Q4),0,(5-SUM(Q4)))))/COUNT(I4:Q4), "") Code:
=IF(COUNT(I4:Q4)>=5, (SUM(I4,J4,L4,N4,O4)+(IF(TRIM(K4)="",0,(5-K4)))+(IF(TRIM(M4)="",0,(5-M4)))+(IF(TRIM(P4)="",0,(5-P4)))+(IF(TRIM(Q4)="",0,(5-Q4))))/COUNT(I4:Q4), "") |
#4
|
|||
|
|||
Hi ArviLaanemets,
I have managed to calculate the new formula that you so made to work for all columns except column BL. I left out the SUM and ended with a value of 4, the correct value is 3.43. Can you please assist? Thank you, B |
#5
|
|||
|
|||
Code:
=if(count(ak4:aq4)>=4, (sum(ao4)+(if(trim(ak4)="",0,(5-ak4)))+(if(trim(al4)="",0,(5-al4)))+(if(trim(am4)="",0,(5-am4)))+(if(trim(an4)="",0,(5-an4)))+(if(trim(ap4)="",0,(5-ap4)))+(if(trim(aq4)="",0,(5-aq4))))/count(ak4:aq4), "") |
#6
|
|||
|
|||
Thank you so very much for all your help.
Sincerely, B |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Odd errors | cyberslugg | PowerPoint | 2 | 02-23-2016 01:26 PM |
Appending unique data from one worksheet to existing data on another worksheet | EdStockton | Excel | 1 | 08-06-2014 11:00 PM |
How to summarise different worksheet to a summary worksheet | samkiewhock | Excel | 1 | 09-06-2012 03:34 AM |
"Auto-populating" data-worksheet to worksheet. | meggenm | Excel | 4 | 02-04-2012 02:04 AM |
How do you fix ost in use errors? | enviroko | Outlook | 0 | 10-23-2008 09:49 AM |