Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-26-2019, 01:24 PM
will soar will soar is offline Need help with #VALUE! errors in worksheet Windows 10 Need help with #VALUE! errors in worksheet Office 2016
Novice
Need help with #VALUE! errors in worksheet
 
Join Date: Feb 2017
Posts: 14
will soar is on a distinguished road
Default 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
Attached Files
File Type: xlsx Safety Climate Data base basic 240519 128Q no999.xlsx (48.6 KB, 8 views)
Reply With Quote
  #2  
Old 05-26-2019, 02:38 PM
Alansidman's Avatar
Alansidman Alansidman is offline Need help with #VALUE! errors in worksheet Windows 10 Need help with #VALUE! errors in worksheet Office 2019
עַם יִשְׂרָאֵל חַי
 
Join Date: Apr 2019
Location: Steamboat Springs
Posts: 79
Alansidman will become famous soon enoughAlansidman will become famous soon enough
Default

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
Reply With Quote
  #3  
Old 05-26-2019, 10:40 PM
ArviLaanemets ArviLaanemets is offline Need help with #VALUE! errors in worksheet Windows 8 Need help with #VALUE! errors in worksheet Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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), "")
Reply With Quote
  #4  
Old 05-27-2019, 04:01 AM
will soar will soar is offline Need help with #VALUE! errors in worksheet Windows 10 Need help with #VALUE! errors in worksheet Office 2016
Novice
Need help with #VALUE! errors in worksheet
 
Join Date: Feb 2017
Posts: 14
will soar is on a distinguished road
Default

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
Reply With Quote
  #5  
Old 05-27-2019, 04:51 AM
ArviLaanemets ArviLaanemets is offline Need help with #VALUE! errors in worksheet Windows 8 Need help with #VALUE! errors in worksheet Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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), "")
Used Notepad to replace semicolons in formula with commas, and somehow forum converted the formula to lowercase! (In notepad were formulas upper case, I used Copy and Paste to transfer formula into post - and got all in lower case!)
Reply With Quote
  #6  
Old 05-28-2019, 02:29 AM
will soar will soar is offline Need help with #VALUE! errors in worksheet Windows 10 Need help with #VALUE! errors in worksheet Office 2016
Novice
Need help with #VALUE! errors in worksheet
 
Join Date: Feb 2017
Posts: 14
will soar is on a distinguished road
Default

Thank you so very much for all your help.

Sincerely,

B
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Odd errors cyberslugg PowerPoint 2 02-23-2016 01:26 PM
Need help with #VALUE! errors in worksheet Appending unique data from one worksheet to existing data on another worksheet EdStockton Excel 1 08-06-2014 11:00 PM
Need help with #VALUE! errors in worksheet How to summarise different worksheet to a summary worksheet samkiewhock Excel 1 09-06-2012 03:34 AM
Need help with #VALUE! errors in worksheet "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

Other Forums: Access Forums

All times are GMT -7. The time now is 03:53 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft