Forum Discussion
Problem with named Range
Hello,
i used followed formula without problems: =MIN(IF($B$2:$B$266<>0;$B$2:$B$266))
also worked: =MIN(IF(NamedArea<>0;NamedArea)) (where NamedArea=$B$2:$B$266)
but when i Named the same Range only every second Cell, like(b2;b4;b6;b8; .... b266) then
IF produce #Value!
Only MIN function works!!! But the IF function produces failures #Value!
does IF function not work with fractured Range?
Thanks for your help!!!
As you have established, Excel will not accept a multi-area range as an argument of an inequality, though some aggregation operators work. If you turn the values from the alternate cells into an array then normal rules prevail. For example
= LET( index, SEQUENCE(ROWS(range)/2,1,1,2), alternateValues, CHOOSEROWS(range,index), MIN(IF(alternateValues<>0, alternateValues)) )works perfectly well.
MIN-IF won't calculate noncontiguous ranges even if the criteria and value ranges are both named items (and both noncontiguous).
The best approach may be to use the entire range but use FILTER to exclude the rows you don't want to include and then find the MIN.
- Hello, thank you also for your explanation. That just confirmed my suspicion. The tip with the filter was very good. Thanks for that too.
greeting - Like this: =MIN(FILTER(B2:B266,(MOD(ROW(B2:B266),2)=0)*(B2:B266<>0)))
Thanks so much, that is a great solution! It works very fine.
How can i use it with the function IFS?
It´s the same Problem, i need only every second Cell.
my formula works but is very long for 69 rows.
It looks like this:
=IFS(G98=G259;B98;G100=G259;B100; ... G234=G259;B234)
How can i Use FILTER with IFS?
All my tries are false 😞
