Address formula

S

Sparcot

Banned user
Joined
Aug 23, 2016
Messages
50
Hello.

I have the following table:

Line1: Product | Minutes1 | Minutes2 | KAPPA
Line2: Oranges | Africa | | 2 | 1 | 15
Line3: Oranges | Africa | 3 | 1 | 15
Line4: Oranges | Africa | 2 | 1 | 15
Line5: Oranges | S. America | 3 | 1 | 15
Line6: Oranges | S. America | 5 | 1 | 15
Line7: Oranges | S. America | 5 | 1 | 15
Line8: Bananas | S. America | 2 | 1 | 16
Line9: Bananas | S. America | 3 | 1 | 16
Line10: Bananas | S. America | 2 | 1 | 16
Line11: Bananas | Africa | 3 | 1 | 16
Line12: Bananas | Africa | 5 | 1 | 16
Line13: Bananas | Africa | 5 | 1 | 16

I need in column F to do the following:
To check if the sum of columns $C and D is not bigger than KAPPA (E column) and if the country (B column) has been changed - then returns 1, otherwise returns 0. The next line to check the same, but already the sum $C$2:D3; next line the same but the sum $C$2:D4 and so on.
Something like that:
=<e2,if(b2=offset(b2,-1,0),0,1),"")
<e2,if(b2=offset(b2,-1,0),0,1),"")
<e2,if(b2=offset(b2,-1,0),0,1),"")
IF(SUM($C$2:D2) "LESS THAN" E2,IF(B2=OFFSET(B2,-1,0),0,1),"")</e2,if(b2=offset(b2,-1,0),0,1),"")


But i need when it gets to another product, i need the sum to start from the line where it was changed. In this case sum $C$8:D8; $C$8:D9; $C$8:D10 and so on.

Hope the explanation was clear.</e2,if(b2=offset(b2,-1,0),0,1),"")
</e2,if(b2=offset(b2,-1,0),0,1),"")
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Re: Addres formula

Sparcot said:
<e2,if(b2=offset(b2,-1,0),0,1),"")
<e2,if(b2=offset(b2,-1,0),0,1),"")
Hope the explanation was clear.
Click to expand...

</e2,if(b2=offset(b2,-1,0),0,1),"")
Hi, a quick tip - it helps a lot if you also include your expected results along with your example data and description.

Not sure I got all the logic right, but hopefully this puts you on the right path.


Excel 2013/2016
ABCDEF
1ProductMinutes1Minutes2KAPPAformula
2OrangesAfrica21151
3OrangesAfrica31150
4OrangesAfrica21150
5OrangesS. America31151
6OrangesS. America51151
7OrangesS. America51151
8BananasS. America21160
9BananasS. America31160
10BananasS. America21160
11BananasAfrica31161
12BananasAfrica51161
13BananasAfrica51161
Sheet1
Cell Formulas
RangeFormula
F2=IF(AND(SUMPRODUCT((A$2:A2=A2)*(C$2:D2))),0,1)
</e2,if(b2=offset(b2,-1,0),0,1),"")
 
Upvote 0
Re: Addres formula

FormR said:
Hi, a quick tip - it helps a lot if you also include your expected results along with your example data and description.

Not sure I got all the logic right, but hopefully this puts you on the right path.

Excel 2013/2016
ABCDEF
Product Minutes1 Minutes2 KAPPAformula
Oranges Africa
Oranges Africa
Oranges Africa
Oranges S. America
Oranges S. America
Oranges S. America
Bananas S. America
Bananas S. America
Bananas S. America
Bananas Africa
Bananas Africa
Bananas Africa

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]1[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=IF(AND(SUMPRODUCT((A$2:A2=A2)*(C$2:D2))<e2,b2=b1< font="">),0,1</e2,b2=b1<>)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hello. It's not actually what I need.

  • F2 is 1 (OK), because it respects the conditions I need: B2<>B1 and SUM($C$2:D2)<e2
    </e2
  • <e2
    F3 is 0 (OK), because it respects the conditions I need: B3=B2 and SUM($C$2:D3)<e3
    </e3
    </e2
  • <e2
    <e3
    F4 is 0 (OK), because it respects the conditions I need: B4=B3 and SUM($C$2:D4)<e4
    </e4
    </e3
    </e2
  • <e2
    <e3
    <e4
    F5 is 1 (OK), because it respects the conditions I need: B5<>B4 and SUM($C$2:D5)<e5
    </e5
    </e4
    </e3
    </e2
  • <e2
    <e3
    <e4
    <e5
    F6 is 1 (nOK - it has to be blank), because it does not respect BOTH conditions I need: B6=B5 (TRUE) BUT SUM($C$2:D6)<e6 (false)
    </e6></e5
    </e4
    </e3
    </e2
  • <e2
    <e3
    <e4
    <e5
    <e6 (false)
    F7 is 1 (nOK - it has to be blank), because it does not respect BOTH conditions I need: B7=B6 (TRUE) BUT SUM($C$2:D7)<e7 (false)
    </e7></e6></e5
    </e4
    </e3
    </e2
  • <e2
    <e3
    <e4
    <e5
    <e6 (false)
    <e7 (false)
    F8 is 0 (nOK - it has to be 1), because it SHOULD respect the conditions I need: B8<>B7 but the sum range should already start in $C$8 (SUM($C$8:D8)<e8), <strong="">because we switched to ANOTHER PRODUCT </e8),></e7></e6></e5
    </e4
    </e3
    </e2
  • <e2
    <e3
    <e4
    <e5
    <e6 (false)
    <e7 (false)
    <e8), <strong=""></e8),></e7></e6></e5
    </e4
    </e3
    </e2
    F9 is 0 (OK), because it respects the conditions I need: B9=B8 and SUM($C$8:D9)<e9
    </e9
  • <e9
    F10 is 0 (OK), because it respects the conditions I need: B10=B9 and SUM($C$8:D10)<e10
    </e10
    </e9
  • <e9
    <e10
    F11 is 1 (OK), because it respects the conditions I need: B11<>B10 and SUM($C$8:D11)<e11
    </e11
    </e10
    </e9
  • <e9
    <e10
    <e11
    F12 is 1 (nOK - it has to be blank), because it does not respect BOTH conditions I need: B12=B11 (TRUE) BUT SUM($C$8:D12)<e12 (false)
    </e12></e11
    </e10
    </e9
  • <e9
    <e10
    <e11
    <e12 (false)
    F13 is 1 (nOK - it has to be blank), because it does not respect BOTH conditions I need: B13=B12 (TRUE) BUT SUM($C$8:D13)<e13 (false)
    </e13></e12></e11
    </e10
    </e9
<e2
<e3
<e4
<e5
<e6 (false)
<e7 (false)
<e8), <strong=""><e9
<e10
<e11
<e12 (false)
<e13 (false)
OMG! I wrote it :)</e13></e12></e11
</e10
</e9
</e8),></e7></e6></e5
</e4
</e3
</e2
Click to expand...
 
Upvote 0
Hi, I still don't really follow the rules, but..

Sparcot said:
<e2,if(b2=offset(b2,-1,0),0,1),"")
<e2,if(b2=offset(b2,-1,0),0,1),"")
But i need when it gets to another product, i need the sum to start from the line where it was changed.
Click to expand...

The below formula does the sum and resets for new products, can you implement the rest yourself?


Excel 2013/2016
ABCDEF
1ProductMinutes1Minutes2KAPPAformula
2OrangesAfrica21153
3OrangesAfrica31157
4OrangesAfrica211510
5OrangesS. America311514
6OrangesS. America511520
7OrangesS. America511526
8BananasS. America21163
9BananasS. America31167
10BananasS. America211610
11BananasAfrica311614
12BananasAfrica511620
13BananasAfrica511626
Sheet1
Cell Formulas
RangeFormula
F2=SUMPRODUCT((A$2:A2=A2)*(C$2:D2))


</e2,if(b2=offset(b2,-1,0),0,1),"")

</e2,if(b2=offset(b2,-1,0),0,1),"")
 
Upvote 0
Re: Addres formula

Corrected with "less than".

Actually it doesn't show me the values I need:


  • F2 is 1 (OK), because it respects the conditions I need: B2<>B1 and SUM($C$2:D2) "less than" D2<e2
    </e2
  • <e2
    F3 is 0 (OK), because it respects the conditions I need: B3=B2 and SUM($C$2:D3)<e3
    </e3
    </e2
    "less than" D3
  • <e2
    F4 is 0 (OK), because it respects the conditions I need: B4=B3 and SUM($C$2:D4)<e4
    </e4
    </e2
    "less than" D4
  • <e2
    F5 is 1 (OK), because it respects the conditions I need: B5<>B4 and SUM($C$2:D5)<e5
    </e5
    </e2
    "less than" D5
  • <e2
    F6 is 1 (nOK - it has to be blank), because it does not respect BOTH conditions I need: B6=B5 (TRUE) BUT SUM($C$2:D6)<e6 (false)
    </e6></e2
    "less than" D6
  • <e2
    F7 is 1 (nOK - it has to be blank), because it does not respect BOTH conditions I need: B7=B6 (TRUE) BUT SUM($C$2:D7)<e7 (false)
    </e7></e2
    "less than" D7
  • <e2
    <e3
    <e4
    <e5
    <e6 (false)
    <e7 (false)
    F8 is 0 (nOK - it has to be 1), because it SHOULD respect the conditions I need: B8<>B7 but the sum range should already start in $C$8 (SUM($C$8:D8)<e8), <strong=""> "less than" D8 because we switched to ANOTHER PRODUCT</e8),></e7></e6></e5
    </e4
    </e3
    </e2
  • <e2
    <e3
    <e4
    <e5
    <e6 (false)
    <e7 (false)
    <e8), <strong=""></e8),></e7></e6></e5
    </e4
    </e3
    </e2
    F9 is 0 (OK), because it respects the conditions I need: B9=B8 and SUM($C$8:D9)<e9
    </e9
    "less than" D9
  • <e9
    F10 is 0 (OK), because it respects the conditions I need: B10=B9 and SUM($C$8:D10)<e10
    </e10
    </e9
    "less than" D10
  • <e9
    F11 is 1 (OK), because it respects the conditions I need: B11<>B10 and SUM($C$8:D11)<e11
    </e11
    </e9
    "less than" D11
  • <e9
    F12 is 1 (nOK - it has to be blank), because it does not respect BOTH conditions I need: B12=B11 (TRUE) BUT SUM($C$8:D12)<e12 (false)
    </e12></e9
    "less than" D12
  • <e9
    F13 is 1 (nOK - it has to be blank), because it does not respect BOTH conditions I need: B13=B12 (TRUE) BUT SUM($C$8:D13)</e9
    "less than" D13
 
Last edited:
Upvote 0
FormR said:
[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=SUMPRODUCT((A$2:A2=A2)*(C$2:D2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

OMG! That Works Perfect! Thanks.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Sum from a table if the key matches a variable array
Replies
3
Views
211
Fluff
Fluff
T
  • Question Question
Formula to sum multiple sequences/ranges of values within an array?
Replies
2
Views
250
Fluff
Fluff
JenniferMurphy
  • Solved
Is Take() the best way to calculate a moving sum?
2 3
Replies
26
Views
1K
JenniferMurphy
JenniferMurphy
ExcelNewbie2020
  • Solved
Extract unique values and sum duplicates
Replies
3
Views
240
alansidman
A
A
  • Question Question
Abstract the Quantity Consumption
Replies
1
Views
93
Rijnsent
Rijnsent

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top