Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Andy
 
Posts: n/a
Default trying to find the smallest with dynamic column changes

I am currently using C through H to find the find the smallest value by using
the following formula
{=min(if((C4:H4)0,(C4:H4))}

But now i want to have automatically find the smallest value if i inserted a
new column. So i used this formula

{=min(if(Indirect("C"&Row()&":"&address(Row(),Colu mn()-1))0,Indirect("C"&Row()&":"&address(Row(),Column( )-1)))}

But its not working i am getting a #value error.

So what is wrong, i dont see it
  #2   Report Post  
Posted to microsoft.public.excel.misc
Domenic
 
Posts: n/a
Default trying to find the smallest with dynamic column changes

Try...

=MIN(IF(INDEX(4:4,3):H40,INDEX(4:4,3):H4))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
"Andy" wrote:

I am currently using C through H to find the find the smallest value by using
the following formula
{=min(if((C4:H4)0,(C4:H4))}

But now i want to have automatically find the smallest value if i inserted a
new column. So i used this formula

{=min(if(Indirect("C"&Row()&":"&address(Row(),Colu mn()-1))0,Indirect("C"&Row(
)&":"&address(Row(),Column()-1)))}

But its not working i am getting a #value error.

So what is wrong, i dont see it

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
creating a bar graph Johnfli Excel Discussion (Misc queries) 0 October 26th 05 08:16 PM
Arithmetical Mode of Criteria in Multiple Non-Adjacent columns Sam via OfficeKB.com Excel Worksheet Functions 4 July 14th 05 09:15 PM
Lookup Table Dilemma Karen Excel Worksheet Functions 2 June 10th 05 08:22 PM
How do I find a column entry closest to a particular value feman007 Excel Worksheet Functions 2 March 9th 05 04:48 PM
How do I find a column entry closest to a particular value feman007 Excel Discussion (Misc queries) 1 March 8th 05 11:22 PM


All times are GMT +1. The time now is 11:57 PM.

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"