Avatar of finnstone
finnstone
 asked on

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
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
finnstone

8/22/2022 - Mon
abbas abdulla

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))
finnstone

ASKER
sorry did not work

#value
abbas abdulla

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?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Subodh Tiwari (Neeraj)

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

finnstone

ASKER
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)

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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
finnstone

ASKER
YES O:Z
ASKER CERTIFIED SOLUTION
Subodh Tiwari (Neeraj)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
finnstone

ASKER
THANKS. I am starting a new question for you
finnstone

ASKER
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
finnstone

ASKER
Subodh Tiwari (Neeraj)

You awarded only 500 bonus points. Why?
finnstone

ASKER
hm I will fix that. sorry i have not used the new scoring system too much yet, so thought i did max points
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
finnstone

ASKER
i will open a ticket
finnstone

ASKER
can you do this one for me though?
finnstone

ASKER
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23