Hi All,
I have attached an example sheet with three columns. I'm trying to get a result in column B that is the product of the corresponding cell in column C and the cell in column C where the value in the row in column A reduces by 1. Terribly explained I know but in the example I would like cell B22 to be 4 as that is C22 multiplied by C18 (A18 being the cell where the number reduces from 3 to 2).
Let me know if this isn't clear and you need further descriptions! Thanks
So in row 23, the value in A22 is 3 and 3-1 = 2. There are many 2's in Col A but you say A18 so I'm thinking the last 2 before row 23? then C18*C23 = 4
Do I have that logic correct?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Not quite, row A indicates the "level" that I'm interested in. I want to multiply the value in column C by the value in the closest level change above the row it is in.
So row 23 is in level 3 and if you look up column A the nearest change in level is in row 18 where the level goes from 3 to 2. At that point I want to multiply C23 by C18. Is that any clearer?
So, if we were in row 9, level 1. The closest level change is in row 7 (2)?
Maybe this ARRAYED formula in B3 copied down?
=INDEX($C$2:$C$88, MAX(ROW($A$1:$A1)*(($A$1:A1)<>($A$2:A2)))-1)*C3
See attachment
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks