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
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
Last Comment
ASKER
sorry did not work
#value
#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?
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?
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.
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))
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!!!!
also can you create one that does the prior 12 columns before 19000. thanks!!!!
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?
Btw do you mean if 19400 is found in N15, you want to sum only O15:Z15?
ASKER
YES O:Z
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
THANKS. I am starting a new question for you
ASKER
here it is, a coutn instead of a sum
https://www.experts-exchange.com/questions/29115304/Count-columns-after-hitting-a-threshold.html#questionAdd
https://www.experts-exchange.com/questions/29115304/Count-columns-after-hitting-a-threshold.html#questionAdd
ASKER
actually, can you do this one first? ...
https://www.experts-exchange.com/questions/29115305/sum-of-prior-12-threshold-now-sum-next-12.html#questionAdd
https://www.experts-exchange.com/questions/29115305/sum-of-prior-12-threshold-now-sum-next-12.html#questionAdd
You awarded only 500 bonus points. Why?
ASKER
hm I will fix that. sorry i have not used the new scoring system too much yet, so thought i did max points
ASKER
i will open a ticket
ASKER
can you do this one for me though?
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Check entering below function using keyboard combinations Ctrl+Shift+Enter
=AVERAGE(INDIRECT(ADDRESS(