Need help with #VALUE! errors in worksheet

ArviLaanemets · #4 05-26-2019, 10:40 PM

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), "")

Oops! This leaves wrong answer for ISBLANK() checks! I post right formula after couple of minutes!

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), "")

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