Hi
im trying to create a "MAX IFS" formula, and am currently using this....
Formula:Please Login or Register to view this content.
But this doesnt return the right value, is there a way to create a maxifs, like you would wth a SUMIFS?
Dan
Hi
im trying to create a "MAX IFS" formula, and am currently using this....
Formula:Please Login or Register to view this content.
But this doesnt return the right value, is there a way to create a maxifs, like you would wth a SUMIFS?
Dan
for example
=MAX(IF(A1:A100="YES",B1:B100,0)*(IF(C1:C100="YES",1,0))
as an array formula, confirmed with ctrl+shift+enter
Hi, yes but it depends on multiple conditions
Please Login or Register to view this content.
Ah right got you thank you
If you use whole columns the formula may be very slow - try this version of your formula with 1000 rows, adjust ranges as required
=IF(OR(D41=4,D29=2),0,MAX(IF(('High,Low,Avg'!$D2:$D1000='Supply Chain Calculator'!$D$30)*('High,Low,Avg'!$H2:$H1000='Supply Chain Calculator'!$D$38)*ISNUMBER(MATCH('High,Low,Avg'!$B2:$B1000,INDEX($A$230:$BI$230,,'Supply Chain Calculator'!$D$31):INDEX($A$230:$BI$230,,'Supply Chain Calculator'!$D$32),0)),'High,Low,Avg'!$U2:$U1000))+D48)
confirmed with CTRL+SHIFT+ENTER
For the last condition I assume that column B just has to match any value in the range created by your INDEX functions....and 'High,Low,Avg'!$U2:$U1000 is the range from which the MAX value should come?
Audere est facere
Yes thats right, i want to look at the max between two dates specifically so the dates are labelled by week numbers 1 through to 52, my index in cells a230 - bi230 is then 1-52, so hopefully it should only look at the max in that selection....
I will try your formula thank you
Thats perfect thank you very much!!!
I had just written out.....
Formula:Please Login or Register to view this content.
But was a long way off being close to your formula.
Thanks again Dan
OK so 'Supply Chain Calculator'!$D$31 and 'Supply Chain Calculator'!$D$32 are the start and end weeks of the range you want to look at and 'High,Low,Avg'!$B:$B contains the week numbers? You can probably simplify a little like this:
=IF(OR(D41=4,D29=2),0,MAX(IF(('High,Low,Avg'!$D2:$D1000='Supply Chain Calculator'!$D$30)*('High,Low,Avg'!$H2:$H1000='Supply Chain Calculator'!$D$38)*('High,Low,Avg'!$B2:$B1000>='Supply Chain Calculator'!$D$31)*('High,Low,Avg'!$B2:$B1000<='Supply Chain Calculator'!$D$32),'High,Low,Avg'!$U2:$U1000))+D48)
You say the week numbers 1 to 52 are in A230:bI230......but that's a range of 61 cells?
Last edited by daddylonglegs; 12-20-2013 at 08:31 AM.
Sorry the week numbers are 57 long, i had to do it that way as its looks at two countrys which have different rates and different arrival dates so the common factor was week commencing, but looked at arrival date to figure that out,
The data starts from E sorry not A
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks