Address formula

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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Re: Addres formula

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

</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

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
1 Product Minutes1 Minutes2 KAPPAformula
2 Oranges Africa21151
3 Oranges Africa31150
4 Oranges Africa21150
5 Oranges S. America31151
6 Oranges S. America51151
7 Oranges S. America51151
8 Bananas S. America21160
9 Bananas S. America31160
10 Bananas S. America21160
11 Bananas Africa31161
12 Bananas Africa51161
13 Bananas Africa51161

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
F2=IF(AND(SUMPRODUCT((A$2:A2=A2)*(C$2:D2))<e2,b2=b1< font="">),0,1</e2,b2=b1<>)

<tbody>
</tbody>

<tbody>
</tbody>

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
 
Upvote 0
Hi, I still don't really follow the rules, but..

<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.

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

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

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