Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Excel 03 - Formula Corrections


  • Please log in to reply
3 replies to this topic

#1 DnDer

DnDer

  • Members
  • 646 posts
  • OFFLINE
  •  
  • Local time:12:04 AM

Posted 19 August 2010 - 10:57 AM

I have cells where the formulas are the same, but the first in the series is coming up with a "#VALUE" error that I can't figure out. The second one creates a "#VALUE" error, presumably because I have the first error sitting above it.

It disappears when you put a value in in the user input value, and gives you a correct solution, but I'd prefer not to see the errors at all, but a blank cell instead.

CELL K15: <user input value>
CELL K16: <user input value>
CELL K17: <user input value>

CELL G21: <user input value> -- CELL I21: <.5, currency format> -- CELL K21: =IF(G21>0,(G21*I21),"")
CELL G22: <user input value> -- CELL I22: <.5, currency format> -- CELL K22: =IF(G22>0,(G22*I22),"")
CELL G23: <user input value> -- CELL I23: <.5, currency format> -- CELL K23: =IF(G23>0,(G23*I23),"")

CELL K25: =SUM(K15:K17,K21:K23)

I don't know how to write complex formulas, and these are someone else's, so I'm not sure what I'm looking at with the =IF statement up there.

What do I need to do, so K21 remains blank until I enter a value in G21? I'm working on the conclusion that once that goes away, I won't see the same error in K25.

BC AdBot (Login to Remove)

 


#2 Geoffc

Geoffc

  • Members
  • 89 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Warragul, Australia
  • Local time:04:04 PM

Posted 20 August 2010 - 02:29 AM

The error code is saying that you're trying to multiply a non-number, and the formula (and the #VALUE function) is not all that complex. You'd really benefit from half an hour or so studying functions and formulas in your computer's Excel HELP file.

...because the solution might depend on which version of Excel you use, and that might need an expensive outsider helping.

Your formula is failing to deal with what it thinks is a non-number, so you could try these simple solutions:
1. Format the (probably troublesome) cells K15 to K17 and G21 to G23 as Number - and re-save the spreadsheet so they stay that way - then check that the problem has gone. But if that's not successful:
2. Include another check in the "IF" statement, like this for example:
For Cell K21: IF(AND(ISNUMBER(G21),G21>0),(G21*I21),"")
Be very careful to get all those brackets and commas in the right places.

If that doesn't work, your next step is not too difficult, but my guess is an Excel guru needs ten miserable minutes on your computer. I live in Southern Australia: can I help?

#3 Eyesee

Eyesee

    Bleepin Teck Shop


  • BC Advisor
  • 3,541 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:In the middle of Kansas
  • Local time:12:04 AM

Posted 20 August 2010 - 06:15 PM

Try Excel's ISERROR (ISERR) function. I use it all the time
Here is a quick tutorial on it
In the beginning there was the command line.

#4 DnDer

DnDer
  • Topic Starter

  • Members
  • 646 posts
  • OFFLINE
  •  
  • Local time:12:04 AM

Posted 23 August 2010 - 07:58 AM

Geoff's solution worked. I will read up on ISERROR, though.

Thank you.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users