Address formula

Sparcot

New Member
Joined
Aug 23, 2016
Messages
11
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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,535
Office Version
  1. 365
Platform
  1. Windows
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.

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";"> Product </td><td style="text-align: right;;"></td><td style=";"> Minutes1 </td><td style=";"> Minutes2 </td><td style=";"> KAPPA</td><td style=";">formula</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";"> Oranges </td><td style=";"> Africa </td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">15</td><td style="text-align: right;background-color: #FFFF00;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";"> Oranges </td><td style=";"> Africa </td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">15</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";"> Oranges </td><td style=";"> Africa </td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">15</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";"> Oranges </td><td style=";"> S. America </td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">15</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";"> Oranges </td><td style=";"> S. America </td><td style="text-align: right;;">5</td><td style="text-align: right;;">1</td><td style="text-align: right;;">15</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";"> Oranges </td><td style=";"> S. America </td><td style="text-align: right;;">5</td><td style="text-align: right;;">1</td><td style="text-align: right;;">15</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";"> Bananas </td><td style=";"> S. America </td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">16</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";"> Bananas </td><td style=";"> S. America </td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">16</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";"> Bananas </td><td style=";"> S. America </td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">16</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";"> Bananas </td><td style=";"> Africa </td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">16</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";"> Bananas </td><td style=";"> Africa </td><td style="text-align: right;;">5</td><td style="text-align: right;;">1</td><td style="text-align: right;;">16</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";"> Bananas </td><td style=";"> Africa </td><td style="text-align: right;;">5</td><td style="text-align: right;;">1</td><td style="text-align: right;;">16</td><td style="text-align: right;;">1</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F2</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">SUMPRODUCT(<font color="Green">(<font color="Purple">A$2:A2=A2</font>)*(<font color="Purple">C$2:D2</font>)</font>)<E2,B2=B1</font>),0,1</font>)</td></tr></tbody></table></td></tr></table><br /></e2,if(b2=offset(b2,-1,0),0,1),"")
 

Sparcot

New Member
Joined
Aug 23, 2016
Messages
11
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
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,535
Office Version
  1. 365
Platform
  1. Windows
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?

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Product</td><td style="text-align: right;;"></td><td style=";">Minutes1</td><td style=";">Minutes2</td><td style=";">KAPPA</td><td style=";">formula</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Oranges</td><td style=";">Africa</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">15</td><td style="text-align: right;background-color: #FFFF00;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Oranges</td><td style=";">Africa</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">15</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Oranges</td><td style=";">Africa</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">15</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Oranges</td><td style=";">S. America</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">15</td><td style="text-align: right;;">14</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Oranges</td><td style=";">S. America</td><td style="text-align: right;;">5</td><td style="text-align: right;;">1</td><td style="text-align: right;;">15</td><td style="text-align: right;;">20</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Oranges</td><td style=";">S. America</td><td style="text-align: right;;">5</td><td style="text-align: right;;">1</td><td style="text-align: right;;">15</td><td style="text-align: right;;">26</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Bananas</td><td style=";">S. America</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">16</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Bananas</td><td style=";">S. America</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">16</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Bananas</td><td style=";">S. America</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">16</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Bananas</td><td style=";">Africa</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">16</td><td style="text-align: right;;">14</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Bananas</td><td style=";">Africa</td><td style="text-align: right;;">5</td><td style="text-align: right;;">1</td><td style="text-align: right;;">16</td><td style="text-align: right;;">20</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Bananas</td><td style=";">Africa</td><td style="text-align: right;;">5</td><td style="text-align: right;;">1</td><td style="text-align: right;;">16</td><td style="text-align: right;;">26</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">A$2:A2=A2</font>)*(<font color="Red">C$2:D2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

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

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

Sparcot

New Member
Joined
Aug 23, 2016
Messages
11

ADVERTISEMENT

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:

Forum statistics

Threads
1,136,314
Messages
5,675,031
Members
419,544
Latest member
MVPDoug

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
Top