Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
ExcelMonkey
 
Posts: n/a
Default Min formula not returning value from Index

I have a formula that looks like this:

=INDEX($E$21:$W$35,MATCH($B40,$B$21:$B$35,0),0)

I want to wrap a Min formula around the result of value
that arises from the formaul and another cell E38:

=Min(E38, INDEX($E$21:$W$35,MATCH($B40,$B$21:$B$35,0),0))

However, when I do this, it seems to produce a value that
is neither E38 nor the value from the Index. It seems to
be pulling a value from the array within the index. Can
I wrap something around the index to ensure that only the
values from E38 and the Index are included in the calc.

Thanks


  #2   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

I just created an array of the same dimensions you used and tested with some
different values and it works fine? I assume you know that if you use zero in
the index formula it will return an array of values from the same row as the
match, thus if the match returns 7 index will return an array of all values
in E27:W27 thus min will pick among all those values and the value in D38. If
you meant to just compare D38 with one value you need to specify the column
number with anything 0 in INDEX

Regards,

Peo Sjoblom

"ExcelMonkey" wrote:

I have a formula that looks like this:

=INDEX($E$21:$W$35,MATCH($B40,$B$21:$B$35,0),0)

I want to wrap a Min formula around the result of value
that arises from the formaul and another cell E38:

=Min(E38, INDEX($E$21:$W$35,MATCH($B40,$B$21:$B$35,0),0))

However, when I do this, it seems to produce a value that
is neither E38 nor the value from the Index. It seems to
be pulling a value from the array within the index. Can
I wrap something around the index to ensure that only the
values from E38 and the Index are included in the calc.

Thanks



  #3   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

The INDEX bit does not specify a single value. From E to W, where do you
want INDEX return a value?

ExcelMonkey wrote:
I have a formula that looks like this:

=INDEX($E$21:$W$35,MATCH($B40,$B$21:$B$35,0),0)

I want to wrap a Min formula around the result of value
that arises from the formaul and another cell E38:

=Min(E38, INDEX($E$21:$W$35,MATCH($B40,$B$21:$B$35,0),0))

However, when I do this, it seems to produce a value that
is neither E38 nor the value from the Index. It seems to
be pulling a value from the array within the index. Can
I wrap something around the index to ensure that only the
values from E38 and the Index are included in the calc.

Thanks


  #4   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

Actually if you array enter it using 19 cells across it will return the
whole row

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"Aladin Akyurek" wrote in message
...
The INDEX bit does not specify a single value. From E to W, where do you
want INDEX return a value?

ExcelMonkey wrote:
I have a formula that looks like this:

=INDEX($E$21:$W$35,MATCH($B40,$B$21:$B$35,0),0)

I want to wrap a Min formula around the result of value that arises from
the formaul and another cell E38:

=Min(E38, INDEX($E$21:$W$35,MATCH($B40,$B$21:$B$35,0),0))

However, when I do this, it seems to produce a value that is neither E38
nor the value from the Index. It seems to be pulling a value from the
array within the index. Can I wrap something around the index to ensure
that only the values from E38 and the Index are included in the calc.

Thanks


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula returning #N/A Excel Worksheet Functions 4 January 27th 05 12:14 AM
revert formula insertion to old method Don't be a pain in the ass Setting up and Configuration of Excel 0 January 24th 05 02:49 PM
Relative Indirect Formula Referencing? Damian Excel Worksheet Functions 1 January 7th 05 05:16 AM
Help with macro formula and variable Huge project Excel Worksheet Functions 0 December 28th 04 02:27 AM
How to use a table index from a formula narendra Excel Worksheet Functions 2 October 31st 04 03:10 PM


All times are GMT +1. The time now is 11:04 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"