LOOK FOR VALUE, THEN TAKE NEXT 12 COLUMNS AND SUM THEM

finnstone
finnstone used Ask the Experts™
on
need a formula to find the column where an array crosses a certain threshold

so need to find the column number where that happens

threshold is 19,000 , looking for the first time an array crosses that mark

note- an array is between columns H-CA

then i need to sum the NEXt twelve columns of data.

so for example, if COL M has 17,500 AND col N has 19,400, and this was first time we crossed 19,000 (ie it didnt happen in colmns A-L, then i  need to sum columns O-Z

need to do this across rows 7-480
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,

Check entering below function using keyboard combinations Ctrl+Shift+Enter

=AVERAGE(INDIRECT(ADDRESS(7,SMALL(IF(H7:CA700>=19000,COLUMN(H7:CA700),),1))& ":"&ADDRESS(700,SMALL(IF(H7:CA700>=19000,COLUMN(H7:CA700),),1)+11),1))

Author

Commented:
sorry did not work

#value
Unfortunately I have no access now to excel but same function is working in google sheets. Check this link https://docs.google.com/spreadsheets/d/11OqfWNeAo99TPlqHO3kzK2CSqkl3Ee98JNscRh83ROY

I will test function using excel tomorrow if you didn't get solution. Can you please post dummy data sample that shows what you are trying to achieve?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
So if a value greater than 19000 (say 19400) is found in column N, do you want to sum the values in the range O7:Z480?
If so, try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

=SUM(OFFSET(INDIRECT(ADDRESS(7,COLUMN(INDEX(H7:CA480,SMALL(IF(H7:CA480>19000,ROW(H7:CA480)-ROW(H7)+1),1),SMALL(IF(H7:CA480>19000,COLUMN(H7:CA7)-COLUMN(H7)+1),1))))),,1,474,12))

Open in new window

Author

Commented:
can you change this ti just sum the row that it is working on please?

also can you create one that does the prior 12 columns before 19000. thanks!!!!
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
I have logged off as it is 2 AM here and on mobile right now. I will check it tomorrow.
Btw do you mean if 19400 is found in N15, you want to sum only O15:Z15?

Author

Commented:
YES O:Z
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Please give this a try...

=SUM(OFFSET(INDEX(H7:CA480,SMALL(IF(H7:CA480>19000,ROW(H7:CA480)-ROW(H7)+1),1),SMALL(IF(H7:CA480>19000,COLUMN(H7:CA7)-COLUMN(H7)+1),1)),,1,1,12))

Open in new window


For prior 12...
=SUM(OFFSET(INDEX(H7:CA480,SMALL(IF(H7:CA480>19000,ROW(H7:CA480)-ROW(H7)+1),1),SMALL(IF(H7:CA480>19000,COLUMN(H7:CA7)-COLUMN(H7)+1),1)),,-12,1,12))

Open in new window

Author

Commented:
THANKS. I am starting a new question for you
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You awarded only 500 bonus points. Why?

Author

Commented:
hm I will fix that. sorry i have not used the new scoring system too much yet, so thought i did max points

Author

Commented:
i will open a ticket

Author

Commented:
can you do this one for me though?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial