LinkBack URL
About LinkBacks
Hi,
I have values in Column B from Cell B3 onward. The differences of these values are in column C from cell C4 onward. In cell H4, I have a reference value with respect to which I want to find sum of differences.
Hi,
I have values in Column B from Cell B3 onward. The differences of these values are in column C from cell C4 onward. In cell H4, I have a reference value with respect to which I want to find sum of differences.
Last edited by Ali2356; 06-15-2022 at 01:18 AM.
I really don't understand your expected results!
These do what you appear to be describing, but do not produce your expected results, so you have a bit more explaining to do:
=SUMPRODUCT(($B$3:$B$27>=$H$4)*(ROW($B$3:$B$27)>=$E$4)*(ROW($B$3:$B$27)<=$F$4)*($C$3:$C$27>0),$C$3:$C$27)
=SUMPRODUCT(($B$3:$B$27>=$H$4)*(ROW($B$3:$B$27)>=$E$4)*(ROW($B$3:$B$27)<=$F$4)*($C$3:$C$27<0),$C$3:$C$27)
=SUMPRODUCT(($B$3:$B$27<=$H$4)*(ROW($B$3:$B$27)>=$E$4)*(ROW($B$3:$B$27)<=$F$4)*($C$3:$C$27>0),$C$3:$C$27)
=SUMPRODUCT(($B$3:$B$27<=$H$4)*(ROW($B$3:$B$27)>=$E$4)*(ROW($B$3:$B$27)<=$F$4)*($C$3:$C$27<0),$C$3:$C$27)
=SUMPRODUCT(($B$3:$B$27>=$H$4)*(ROW($B$3:$B$27)>=$E$4)*(ROW($B$3:$B$27)<=$F$4)*($C$3:$C$27>0))
=SUMPRODUCT(($B$3:$B$27>=$H$4)*(ROW($B$3:$B$27)>=$E$4)*(ROW($B$3:$B$27)<=$F$4)*($C$3:$C$27<0))
=SUMPRODUCT(($B$3:$B$27<=$H$4)*(ROW($B$3:$B$27)>=$E$4)*(ROW($B$3:$B$27)<=$F$4)*($C$3:$C$27>0))
=SUMPRODUCT(($B$3:$B$27<=$H$4)*(ROW($B$3:$B$27)>=$E$4)*(ROW($B$3:$B$27)<=$F$4)*($C$3:$C$27<0))
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
In J4
=SUMIFS($C$3:C$23,C$3:C$23,">0",$B$3:$B$23,">=" &H4)
in J10
=COUNTIFS(C$3:C$23,">0",$B$3:$B$23,">=" &H4)
You should be able to work out the other formulae.
Check you results: B11 and B20 are both 50 with +ve differences
And you cannot have both tests >= or <= : either >=50 and <50 OR >50 and <= 50
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
@John
I think we are meant to include the sequence row numbers, hence my confusion. The OP seems to be asking for one thing, but giving expected results for something else.
Anomalies highlighted to the right:
AliGW on MS365 Beta Channel (Windows 11) 64 bit
Sheet: Sheet1
AliGW on MS365 Beta Channel (Windows 11) 64 bit
Sheet: Sheet1
@Ali,
You are right .. hence your use of SUMPRODUCT!I think we are meant to include the sequence row numbers
At this juncture, som input from the OP would be really helpful.
Nothing ...
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
Hi, AliGW
Please give me some time my issue is not resolved yet.
Also i am writing a reply in more depth that would explain my expected results. Also this will eliminate the Anomalies coming at B11, B16 and B20.
I would take 30 to 40 minutes more to write all my detailed reply explaining it all.
Thanks
OK - thanks for checking in. It would have been a good idea to mention this sooner, though, otherwise helpers are left hanging. Please bear this in mind in future.
These match your expected results:
AliGW on MS365 Beta Channel (Windows 11) 64 bit
=SUMPRODUCT(($B$3:$B$27>$H$4)*(ROW($B$3:$B$27)>=$E$4)*(ROW($B$3:$B$27)<=$F$4)*($C$3:$C$27>0),$C$3:$C$27) =SUMPRODUCT(($B$3:$B$27>=$H$4)*(ROW($B$3:$B$27)>=$E$4)*(ROW($B$3:$B$27)<=$F$4)*($C$3:$C$27<0),$C$3:$C$27) =SUMPRODUCT(($B$3:$B$27<=$H$4)*(ROW($B$3:$B$27)>=$E$4)*(ROW($B$3:$B$27)<=$F$4)*($C$3:$C$27>0),$C$3:$C$27) =SUMPRODUCT(($B$3:$B$27<$H$4)*(ROW($B$3:$B$27)>=$E$4)*(ROW($B$3:$B$27)<=$F$4)*($C$3:$C$27<0),$C$3:$C$27) =SUMPRODUCT(($B$3:$B$27>$H$4)*(ROW($B$3:$B$27)>=$E$4)*(ROW($B$3:$B$27)<=$F$4)*($C$3:$C$27>0)) =SUMPRODUCT(($B$3:$B$27>=$H$4)*(ROW($B$3:$B$27)>=$E$4)*(ROW($B$3:$B$27)<=$F$4)*($C$3:$C$27<0)) =SUMPRODUCT(($B$3:$B$27<=$H$4)*(ROW($B$3:$B$27)>=$E$4)*(ROW($B$3:$B$27)<=$F$4)*($C$3:$C$27>0)) =SUMPRODUCT(($B$3:$B$27<$H$4)*(ROW($B$3:$B$27)>=$E$4)*(ROW($B$3:$B$27)<=$F$4)*($C$3:$C$27<0))
Sheet: Sheet1
However, to make them do so, I have had to change the formula so that some use >=50, some >50, some <=50 and some <50 - this is a weird inconsistency, if it is intended.
Last edited by AliGW; 06-09-2022 at 05:14 AM. Reason: Workbook attached.
Hi, AliGW
My expected results are those mentioned in the file. I have calculated them manually but these results i want.
Now i came to know that there is a error (Anomalies) that comes on at B11, B16 and B20. I know due to this my expected result would not come up.
Please let me explain in an other way with which Anomalies would be resolved
From here onward First Condition starts till C11.
C4
As my values are from cell B3 onward. Now difference in cell C4 comes out as
=B4-B3
As, B4=28 and B3=25 so,
=28-25
=3
First Condition
(If any of the two values of (B3 and B4) is equal to or less than reference value 50 and other one must less than 50. Then this difference value will go to L4 if it +ive and M4 if it -ive.)
As in this C4 case both B4 and B3 are less than Reference value. so their output value +3 should go to the sum formula of L4.
C5
Difference in cell C5 comes out as
=B5-B4
As, B5=35 and B4=28 so,
=35-28
=7
As in this C5 case both B5 and B4 are less than Reference value. so their output value +7 should go to the sum formula of L4.
C6
Difference in cell C6 comes out as
=B6-B5
As, B6=30 and B5=35 so,
=30-35
=-5
As in this C6 case both B6 and B5 are less than Reference value. so their output value -5 should go to the sum formula of M4.
C7
Difference in cell C7 comes out as
=B7-B6
As, B7=25 and B6=30 so,
=25-30
=-5
As in this C7 case both B7 and B6 are less than Reference value. so their output value -5 should go to the sum formula of M4.
C8
Difference in cell C8 comes out as
=B8-B7
As, B8=30 and B7=25 so,
=30-25
=+5
As in this C8 case both B8 and B7 are less than Reference value. so their output value +5 should go to the sum formula of L4.
C9
Difference in cell C9 comes out as
=B9-B8
As, B9=35 and B8=30 so,
=35-30
=+5
As in this C9 case both B9 and B8 are less than Reference value. so their output value +5 should go to the sum formula of L4.
C10
Difference in cell C10 comes out as
=B10-B9
As, B10=40 and B9=35 so,
=40-35
=+5
As in this C10 case both B10 and B9 are less than Reference value. so their output value +5 should go to the sum formula of L4.
C11
Difference in cell C11 comes out as
=B11-B10
As, B11=50 and B10=40 so,
=50-40
=+10
As in this C11 case, B11 is equal to reference value 50 and B10 is less than Reference value. So, Condition First is Satisfied so their output value +10 should go to the sum formula of L4.
From here onward Second Condition starts till C16.
C12
Difference in cell C12 comes out as
=B12-B11
As, B12=52 and B11=50 so,
=52-50
=+2
Second Condition
(if any of the two values of (B12 and B11) is equal to or Greater than reference value 50 and other one must Greater than 50. Then this difference value will go to J4 if it +ive and K4 if it -ive.)
As in this C12 case, B12 is greater than reference value 50 and B11 is equal to Reference value. So, Condition Second is Satisfied so their output value +2 should go to the sum formula of J4.
C13
Difference in cell C13 comes out as
=B13-B12
As, B13=55 and B12=52 so,
=55-52
=+3
As in this C13 case, both B13 and B12 are greater than Reference value. So, Condition Second is Satisfied so their output value +3 should go to the sum formula of J4.
C14
Same above case for Cell C14.
C15
Difference in cell C15 comes out as
=B15-B14
As, B15=55 and B14=60 so,
=55-60
=-5
As in this C15 case, both B15 and B14 are greater than Reference value. So, Condition Second is Satisfied so their output value -5 should go to the sum formula of K4.
C16
Difference in cell C16 comes out as
=B16-B15
As, B16=50 and B15=55 so,
=50-55
=-5
As in this C16 case, both B16 and B15 are greater than Reference value. So, Condition Second is Satisfied so their output value -5 should go to the sum formula of K4.
C17
Difference in cell C17 comes out as
=B17-B16
As, B17=45 and B16=50 so,
=45-50
=-5
As in this C17 case, B16 is equal to Reference value 50 and B17 is less than reference value 50, so condition First is satisfied. So, their output value -5 should go to the sum formula of M4.
And So on for other cells.
So, given the mistakes you made with your data, do the formulae I gave you in post #2 now work? If not, do the ones in post #11 work?
Let me know before I try to get my head around post #12.
If you want more help, then please provide a corrected workbook with accurate expected results.
Last edited by AliGW; 06-09-2022 at 05:21 AM. Reason: Typo fixed.
Hi AliGW
Thank you so much. Formula in Post 12 Works well.
Thank you again for your help.
Glad to have helped.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks