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

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
finnstoneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

abbas abdullaCommented:
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))
0
finnstoneAuthor Commented:
sorry did not work

#value
0
abbas abdullaCommented:
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?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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

1
finnstoneAuthor 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!!!!
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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?
0
finnstoneAuthor Commented:
YES O:Z
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
finnstoneAuthor Commented:
THANKS. I am starting a new question for you
0
finnstoneAuthor Commented:
0
finnstoneAuthor Commented:
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You awarded only 500 bonus points. Why?
0
finnstoneAuthor Commented:
hm I will fix that. sorry i have not used the new scoring system too much yet, so thought i did max points
0
finnstoneAuthor Commented:
i will open a ticket
0
finnstoneAuthor Commented:
can you do this one for me though?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.