Why #VALLUE! error on "correct" formula in Excel 2007?

**pollyannadanna** · 12-14-2013, 11:31 PM

I'm getting an unexpected #VALUE! error in Excel 2007 for this formula (sample file attached):

=SUM(C95+(B96:B99))

All cells are the same format, Currency. When I step through the calculation, the second part, (B96:B99), generates the error. Believe it or not, when I entered the formula without the closing second paren, Excel suggested THIS formula as the correction and then returned the #VALUE! error. Does anyone know why this is happening or how to fix it? I'm sure I've used this type of formula before and had no problems.

Kristi

**protonLeah** · 12-14-2013, 11:33 PM

I don't see your attachment.
Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**pollyannadanna** · 12-14-2013, 11:42 PM

Sorry--new user. I selected the doc but didn't upload! Attached now, short and sweet example.

**FDibbins** · 12-14-2013, 11:45 PM

Welcome to the forum

Havnt looked at your file (I wil though), but try putting the C95+ outsude the SUM()

**Tony Valko** · 12-14-2013, 11:47 PM

You get the #VALUE! error because that formula is an array formula and needs to be entered using the key combination of CTRL, SHIFT, ENTER. That is, hold down both the CTRL and SHIFT keys then press ENTER.

What the formula is doing is this:

SUM(C95+B96,C95+B97,C95+B98,C95+B99)

Is that your intention?

**FDibbins** · 12-14-2013, 11:49 PM

or perhaps this...
=SUM(B96:B99,C95)

What exactly are you trying to achieve here? maybe subtotal(9,range) would help?

**pollyannadanna** · 12-15-2013, 12:28 AM

Yikes, not at all my intention! I meant to do simple math and thought I could use a range for B96:B99 rather than listing them out, like this:

B95-(B89-C89)+B96+B97+B98+B99

The value I want is $1,306.75 (C95) + $430.00 (B96+B97+B98+B99) = $1,736.75.

I realize I could've just created 2 subtotals and added them together--didn't occur to me that what I was trying wouldn't work. I feel pretty silly now. So I can learn/understand, is my use of the range what turned this into an array--or I can just look that up myself in the Excel docs.

**Tony Valko** · 12-15-2013, 10:17 AM

Originally Posted by pollyannadanna

is my use of the range what turned this into an array

It's an array formula because you're performing the action of adding the value of cell C95 to each cell value in the range B95:B99.

=SUM(C95+(B96:B99))

Why #VALLUE! error on "correct" formula in Excel 2007?

LinkBack

Thread Tools

Rate This Thread

Display

Why #VALLUE! error on "correct" formula in Excel 2007?

Re: Why #VALLUE! error on "correct" formula in Excel 2007?

Re: Why #VALLUE! error on "correct" formula in Excel 2007?

Re: Why #VALLUE! error on "correct" formula in Excel 2007?

Re: Why #VALLUE! error on "correct" formula in Excel 2007?

Re: Why #VALLUE! error on "correct" formula in Excel 2007?

Re: Why #VALLUE! error on "correct" formula in Excel 2007?

Re: Why #VALLUE! error on "correct" formula in Excel 2007?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Excel 2007: How to Convert "5/2/2013" to "May" then subtract a Month so it's "Apr"

solution for the blank cell using the IF function

[SOLVED] VBA Excel 2007 : Autofit Merge Cells Error "Run-time error '13': Type Mismatch"

Excel 2007 : =IF formula to identify "Yes" or "No" if between two dates

[SOLVED] How do I correct Runtime error "53" in excel 2003

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions