Please see the file attached.
Column H returns the min values of the range B:F using aggregate formula to ignore #N/A values in that range.
How can I exclude columns C and E from the range? Manual selection of which columns to include in the range is not an option here, because my range can consist of 20+ columns and the columns which need to be excluded can vary quite often, so I am looking for some convenient way to change the columns which would be excluded from the range.
Thanks!!
What is the rule for deciding which columns to exclude/include?
What I mean is, how are you going to decide which ones to have or not have? As input you feed into the formula.
Like, if you could set B1:F1 to {1,0,1,0,1} to use as on/off switches, "1" for include and "0" for exclude, then you could do it with a formula like:
Please Login or Register to view this content.
Click the [* Add Reputation] Button to thank people for help!
Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.
ben, I can do it just by manually editing the formula (if the formula is something like =excl(B,C,E, etc..)
or I can also use your method by switching them.
could you please elaborate more, how to set B1:F1 to {1,0,1,0,1} switches?
In H2, copied down:As Ben suggested, manually enter 1 in row 1 of each column you wish to include in the calculation. (...assuming you wish to exclude the same columns for every row)Please Login or Register to view this content.
Last edited by leelnich; 03-06-2018 at 07:53 PM.
Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee
I inserted the formula and I get $VALUE? as a result, could you please check it?
@kao, Ben's formula from post #2 is an ARRAY FORMULA. You must press CTRL+SHIFT+ENTER (instead of ENTER) when editing or pasting it in the formula bar. This tells Excel to expect arrays. If successful, {} brackets will surround the formula text.
Did you try post#4 proposal? It can be entered normally.
Last edited by leelnich; 03-06-2018 at 08:21 PM.
leelnich, thanks for advising. However ctrl+shift+enter does not work too. any idea why?
the post #2 works, thank you for that! do you know why this one does not work?
Please Login or Register to view this content.
1) CSE entry of Ben's formula worked fine for me. No idea why it's not working for you.
2) Oddly, AGGREGATE only allows array inputs with function types that use a "k" parameter* - functions 14-19, including LARGE and SMALL.
If configured as SUM, COUNT, MAX, MIN, etc., it always returns #VALUE errors with arrays.
*http://www.exceluser.com/excel_help/functions/function-aggregate.htm
And...You're welcome, thank you for the rep! If concluded, please mark your thread as SOLVED (Thread Tools above post #1). Regards – Lee
Last edited by leelnich; 03-06-2018 at 09:34 PM.
leelnich, Bens formula works for me too, I messed up with {} at first
There is another issue, please see the attached photo.
column P (min Ask) uses =aggregate(5;6)
column Q uses aggregate(15;6)
column R uses Bens array formula.
The issue is, that in some cases these formulas return different values, where they all should give the same result.
The range is B:O and they seem to mess up when the MIN value is in column G, somehow these formulas do not include column G into the range.
Please take a look at rows 280, 282 and 285
row 280:
=aggregate(5;6) returns value of column D, when the lowest value in the range is in column G
=aggregate(15;6) returns value of the column G, which seems alright
Ben's array formula returns value of column D, when the lowest value in the range is in column G
Of course, I could probably just use aggregate(15;6) and it should work, but I still want to find the glitch here, because it looks like there is something here.
Any suggestions of how to fix this?
Conditional Formatting also by some reason excludes column G from the range.
What could cause this here?
Please post the spreadsheet itself, not a screencap.
If you are still using the CF I set up for you, it only covers up to column F: extend the range in the Applies To box for each of the rules.
Last edited by AliGW; 03-08-2018 at 02:05 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
sorry.
rows: 27, 34, 130, 159, 179-186, 191, 207, 231
In this case, formulas exclude column C from the range
kao- the problem lies with your data. Some "numbers" are actually text, which is ignored by functions looking for numbers - unless it's used in a mathematical expression, as in the AGGREGATE(15,6...) formula. That's why you got different answers.
If desired, you can coerce your VLOOKUP returns into numbers (if available) as below:Please Login or Register to view this content.
Last edited by leelnich; 03-07-2018 at 07:59 PM.
Column C is pulling from tab "kr", which is storing the values as text strings, not as numbers.
Because only the middle formula (leelnich's AGGREGATE("small") function) is doing math on the center range, it's the only one that's "forcing" column C to be assessed as a number; the others are silently ignoring it as "text" instead of a "number".
You need to do something to force the text into numbers, perhaps something like:
That +0 will force text-into-numbers.Please Login or Register to view this content.
Also, if you can, feed the function something like B1:D10000; feeding functions whole entire column references, "B:D" like that, is kind of a bad practice.
EDIT:
Guh,
Ninja'd by leelnich, right down to the suggestion.
Great minds...
BTW, I tried to post that an hour ago, but the website was acting up. Did you experience any problems?
Thank you guys, that helped a lot!!!
can you elaborate a little bit more please?Also, if you can, feed the function something like B1:D10000; feeding functions whole entire column references, "B:D" like that, is kind of a bad practice.
I would have to do loads of changing...
It is really a problem ONLY for array formulae and SUMPRODUCT. In those cases, the uncontrolled use of whole column references can slow your sheet up. Your VLOOKUP will be fine.
https://fastexcel.wordpress.com/2015...a-or-bad-idea/
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks