LinkBack URL
About LinkBacks
Can anyone alter the formula in column L to make it so that the sum of M() and N() must be >=1 in order to make computations?
For example: in the attached file the formula would begin summing K values at K8 because the sum of M7 & N7 >= 1.
Can anyone alter the formula in column L to make it so that the sum of M() and N() must be >=1 in order to make computations?
For example: in the attached file the formula would begin summing K values at K8 because the sum of M7 & N7 >= 1.
May be this......
Please Login or Register to view this content.
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
In that formula the value of K7 was summed. I'm looking to sum numbers occurring after K7. So once M()+N() >=1 THEN start summing all values in the row immediately beneath. Does that make sense?
The desired formula would produce the results in column L in this example file.
OK. Try this.......
Please Login or Register to view this content.
Last edited by sktneer; 11-29-2013 at 12:41 AM.
I hope this is OK by the moderators... I'm not really sure why my last thread was taken down, but I think I have to post in this thread...?
Do you think you can help me find another formula based upon the file EXAMPLE 5A?
I'm looking for a formula that will produce what the values of M() and N() are when M()+N() = 3.
So.... in this example M() + N() = 3 at row 30 (or M30 + N30). Therefor the formula would produce a result of 3-0. I'm GUESSING that this formula has to be split into two columns so I figured the formulas would go in M2 & N2. M2 would = 3 and N2 would = 0.
IF one were to copy and paste that SAME formula into AD2 & AE2, for example, AD() + AE() = 3 at row 32 (or AD32 + AE32 = 3). Thus the formula would produce the result of 1-2.
If this is your data:
Data Range
Tell us what result(s) you expect and where the results should be located. Be VERY specific!
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
In EXAMPLE 5B you'll see:
M2 & N2 is where I would like the formulas to be entered and the results to appear. I highlighted that green.
The formula for each column will cover data M4 to M165 and N4 to N165. I highlighted that yellow.
The first time where the sum of adjacent cells in columns M & N is = to "3" & what the value of each cell is is what I'm looking for. I highlighted that grey.
I can't download your file.
I have a 50kb download size limit.
Can you make up a SMALL sample file with ~20 rows worth of data?
20 rows worth of data is plenty.
Try this file.
OK, let's see if I understand what you want to do.
In the smaller sample file...
If on row 15, M15 = 2 and N15 = 1 then you want M2 to = 2 and N2 to = 1?
Yes. That is correct.
Try this array formula** entered in M2 and copied across to N2:
=INDEX(M4:M44,MATCH(3,$M4:$M44+$N4:$N44,0))
If no cells will sum to 3 then this version** will return a blank instead of an error:
=IFERROR(INDEX(M4:M44,MATCH(3,$M4:$M44+$N4:$N44,0)),"")
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
That appears to work for those columns. Do you know how I can duplicate the same results into:
P2 & Q2
T2 & U2
W2 & X2
AA2 & AB2
AD2 & AE2
AH2 & AI2
AK2 & AL2
Copy and pasting doesn't seem to work.
You'll have to change the ranges accordingly:
Entered in P2 and copied across to Q2:
=INDEX(P4:P44,MATCH(3,$P4:$P44+$Q4:$Q44,0))
Entered in T2 and copied across to U2:
=INDEX(T4:T44,MATCH(3,$T4:$T44+$U4:$U44,0))
etc
etc
etc
I think that worked. Thanks.
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks