Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-09-2018, 01:29 AM
NickFazer NickFazer is offline Ignoring #VALUE in formula calculations Windows 7 64bit Ignoring #VALUE in formula calculations Office 2003
Novice
Ignoring #VALUE in formula calculations
 
Join Date: Sep 2018
Posts: 8
NickFazer is on a distinguished road
Default Ignoring #VALUE in formula calculations

Hi




Is there a Function or can a macro be used that will ignore #VALUE in a sum formula?



Thanks



Nick
Reply With Quote
  #2  
Old 11-09-2018, 02:55 AM
Marcia's Avatar
Marcia Marcia is offline Ignoring #VALUE in formula calculations Windows 7 32bit Ignoring #VALUE in formula calculations Office 2007
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

If the reference in your formula includes a cell with an error, it will return an error value. But if you mean you would not want error values appearing after executing your formula, use =IFERROR(your formula),"")
Reply With Quote
  #3  
Old 11-09-2018, 04:13 AM
NickFazer NickFazer is offline Ignoring #VALUE in formula calculations Windows 7 64bit Ignoring #VALUE in formula calculations Office 2003
Novice
Ignoring #VALUE in formula calculations
 
Join Date: Sep 2018
Posts: 8
NickFazer is on a distinguished road
Default

Hi Marcia


Thank you for your reply, still having an issue adding the IFERROR function to work with the formula below

=MID(E3,2,10)+0


I have tried =IFERROR((MID(E3,2,10)+0),"")

Any ideas why this is wrong?


Thanks

Nick
Reply With Quote
  #4  
Old 11-09-2018, 05:36 AM
Marcia's Avatar
Marcia Marcia is offline Ignoring #VALUE in formula calculations Windows 7 32bit Ignoring #VALUE in formula calculations Office 2007
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Try: =IFERROR(MID(E3,2,10)+0,""), this will return a blank cell, without the IFERROR function you will receive an error message. May I know the string in E3 from which you are trying to extract 10 characters?
Reply With Quote
  #5  
Old 11-09-2018, 06:58 AM
NickFazer NickFazer is offline Ignoring #VALUE in formula calculations Windows 7 64bit Ignoring #VALUE in formula calculations Office 2003
Novice
Ignoring #VALUE in formula calculations
 
Join Date: Sep 2018
Posts: 8
NickFazer is on a distinguished road
Default

Hi Marcia

The value I am extracting is between M1 and M12, so 10 is OTT, M value is selected from a drop down list, so I don't know if that complicates matters. I tried the IFERROR formula and now returning #NAME.


Thanks


Nick
Reply With Quote
  #6  
Old 11-09-2018, 07:04 AM
NBVC's Avatar
NBVC NBVC is offline Ignoring #VALUE in formula calculations Windows 10 Ignoring #VALUE in formula calculations Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

IFERROR is not available in Excel 2003...




try:


=IF(ISERROR(MID(E3,2,10)+0),"",MID(E3,2,10)+0)
Reply With Quote
  #7  
Old 11-09-2018, 07:24 AM
NickFazer NickFazer is offline Ignoring #VALUE in formula calculations Windows 7 64bit Ignoring #VALUE in formula calculations Office 2003
Novice
Ignoring #VALUE in formula calculations
 
Join Date: Sep 2018
Posts: 8
NickFazer is on a distinguished road
Default

That has sorted it.


Many Thanks to you both, better lobby for an Excel update to this decade
Reply With Quote
  #8  
Old 11-09-2018, 03:26 PM
Marcia's Avatar
Marcia Marcia is offline Ignoring #VALUE in formula calculations Windows 7 32bit Ignoring #VALUE in formula calculations Office 2007
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

[quote=NBVC;135290]IFERROR is not available in Excel 2003.


OMG, it never entered my mind to check the version Nick is using. Thank you.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Orphan/widow control ignoring single line seanspotatobusiness Word 8 07-17-2018 03:46 PM
Ignoring #VALUE in formula calculations Any way to ignore neighboring cell while ignoring blank cells in SUMPRODUCT? dominicrudin Excel 1 01-08-2017 10:38 AM
Calculations etobias Word 2 11-09-2016 10:14 AM
View by date ignoring folders? adamgram Outlook 0 09-12-2013 07:22 AM
Ignoring #VALUE in formula calculations Merge Field Ignoring Zeros Welshie82 Mail Merge 2 05-04-2012 01:56 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:11 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