Named Range #VALUE error with one small change to formula - why?

[N0043]

Trying to get excel to define a range between 2 cells. One cell has this formula:

=IF(C3="",,SUM(VLOOKUP(A3,NBA!A3:AQ32,37,FALSE),VLOOKUP(B3,NBA!A3:AQ32,37,FALSE),VLOOKUP(A3,NBA!A3:AQ32,40,FALSE),VLOOKUP(B3,NBA!A3:AQ32,40,FALSE))/2-(Average(SQRT(VLOOKUP(A3,NBA!A3:AQ32,42,FALSE)^2+VLOOKUP(B3,NBA!A3:AQ32,42,FALSE)^2),(SQRT(VLOOKUP(A3,NBA!A3:AQ32,43,FALSE)^2 + VLOOKUP(B3,NBA!A3:AQ32,43,FALSE)^2)))))

And the other is identical, except for a + instead of a minus before the *average* command.

This formula fetches data from other spreadsheets to first define the weighted average, and then a combined standard deviation, to +/- said StDev from the WtAvg.

The leftmost cell finds, rounded to the nearest whole number,
WtAvg - StDev,

The rightmost cell finds, rounded to the nearest whole number,
WtAvg +StDev

I want excel to clearly define that there is a range between these two outputted values, but the named range keeps outputting #VALUE: an array value could not be found.

I've been trying for hours to resolve this with no luck, please help I'm losing my sanity over this LOL.

[wildecoyote1966]

Add a file so we can have a look

[N0043]

Unable to, apparently you have to post in this forum a number of times to share links.

[wildecoyote1966]

try


HOW TO ATTACH YOUR SAMPLE WORKBOOK:
wildecoyote1966 Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

[AliGW]

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

[wildecoyote1966]

ok Thanks AliG

[AliGW]

Welcome to the forum.

Unable to, apparently you have to post in this forum a number of times to share links.

No need to post a link - that's why we have an attachment facility.

As has already been mentioned, there are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

[Glenn Kennedy]

OK. I can follow what the formula is doing... but I have no idea what you eman by:

I want excel to clearly define that there is a range between these two outputted values, but the named range keeps outputting #VALUE: an array value could not be found.


On this sample sheet, your formula is in A1 and my (no better/no worse) deconstruction/reconstruction of it is in B1.

What is your expected final result? Where do you expect to see it?

Add it to this sheet and post it in a new post in THIS thread.