If statement issue

[Monjavz]

I need help please, I have various conditions and got this far

=IF(AND(W687>0;X686>0);(W687*Y687*G687)+X686*Y687*G687;0)

only problem is that if cell W687 is negative it should still perform the calculation for X686 if this cell is positive.

How can I change the formula to do this.

Thank you very much

 

[Special-K99]

Do you mean like this?

=IF(OR(W687<0,AND(W687>0;X686>0));(W687*Y687*G687)+X686*Y687*G687;0)

 

[steve the fish]

Do you need OR rather than AND?

 

[steve the fish]

Or just use ABS on the cell in question which turns a negative number positive.

 

[Monjavz]

Thanks I changed it to OR, but now it is still not calculating correctly.

What in short happens is that I have a running balance that changes with movements + or - and there are certain days that needs to be calculated on the opening and closing balance. And when the running balance is negative no calc should take place only the opening balance portion should calculate

Rate MVT Quantity Running total Days Costs
2000 (opening balance)
0.80 -100 1900 7 11 120.00

So for 6 days the balance was 2000 and then for one day it is 1900 at a rate of 0.80

Then what makes it even further complicated is that the running total runs into a negative and then you will only have to calculate cost for the #days for the opening balance if the closing balance after it goes negative does not incur any costs. Hope this makes sense. I'm so confused as I have tried many means still not getting the answer right and the sheet has numerous lines I need a formula that works that I can pull down

Thanks

 

[steve the fish]

Im afraid that its hard to understand what you mean. How about providing some sample data with expected results of the formula?

 

[Monjavz]

Steve I'm struggling to ad the sample data. I tried to add the add in. Any suggestions?

 

[steve the fish]

Put it in excel. Copy the cells. Paste them here.

 

[Monjavz]

[TABLE="width: 938"]
<colgroup><col><col><col><col span="2"><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]MovementDate[/TD]
[TD]Date[/TD]
[TD]Days[/TD]
[TD]Rate check[/TD]
[TD]BTW Rate[/TD]
[TD] MovementQty [/TD]
[TD] Running Total [/TD]
[TD] Days [/TD]
[TD] Berging [/TD]
[TD] Correct answer [/TD]
[/TR]
[TR]
[TD="align: right"]2018/02/28[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2018/04/01[/TD]
[TD] 2 065,72 [/TD]
[TD] 2 065,72 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2018/03/07[/TD]
[TD="align: right"]2018/02/28[/TD]
[TD] 7 [/TD]
[TD] 0,80 [/TD]
[TD] 1,14 [/TD]
[TD]- 100,00 [/TD]
[TD] 1 965,72 [/TD]
[TD] 7 [/TD]
[TD] 11 008,03 [/TD]
[TD] 11 488,03 [/TD]
[/TR]
[TR]
[TD="align: right"]2018/03/13[/TD]
[TD="align: right"]2018/03/07[/TD]
[TD] 6 [/TD]
[TD] 0,80 [/TD]
[TD] 1,14 [/TD]
[TD]- 35,00 [/TD]
[TD] 1 930,72 [/TD]
[TD] 6 [/TD]
[TD] 9 267,46 [/TD]
[TD] 9 407,46 [/TD]
[/TR]
[TR]
[TD="align: right"]2018/03/13[/TD]
[TD="align: right"]2018/03/13[/TD]
[TD] - [/TD]
[TD] 0,80 [/TD]
[TD] 1,14 [/TD]
[TD]- 100,00 [/TD]
[TD] 1 830,72 [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD="align: right"]2018/03/19[/TD]
[TD="align: right"]2018/03/13[/TD]
[TD] 6 [/TD]
[TD] 0,80 [/TD]
[TD] 1,14 [/TD]
[TD]- 200,00 [/TD]
[TD] 1 630,72 [/TD]
[TD] 6 [/TD]
[TD] 7 827,46 [/TD]
[TD] 8 627,46 [/TD]
[/TR]
[TR]
[TD="align: right"]2018/03/23[/TD]
[TD="align: right"]2018/03/19[/TD]
[TD] 4 [/TD]
[TD] 0,80 [/TD]
[TD] 1,14 [/TD]
[TD]- 100,00 [/TD]
[TD] 1 530,72 [/TD]
[TD] 4 [/TD]
[TD] 4 898,30 [/TD]
[TD] 5 138,30 [/TD]
[/TR]
[TR]
[TD="align: right"]2018/03/23[/TD]
[TD="align: right"]2018/03/23[/TD]
[TD] - [/TD]
[TD] 0,80 [/TD]
[TD] 1,14 [/TD]
[TD]- 35,00 [/TD]
[TD] 1 495,72 [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD="align: right"]2018/03/26[/TD]
[TD="align: right"]2018/03/23[/TD]
[TD] 3 [/TD]
[TD] 0,80 [/TD]
[TD] 1,14 [/TD]
[TD]- 90,72 [/TD]
[TD] 1 405,00 [/TD]
[TD] 3 [/TD]
[TD] 3 372,00 [/TD]
[TD] 3 517,15 [/TD]
[/TR]
[TR]
[TD="align: right"]2018/03/28[/TD]
[TD="align: right"]2018/03/26[/TD]
[TD] 2 [/TD]
[TD] 0,80 [/TD]
[TD] 1,14 [/TD]
[TD] 0,41 [/TD]
[TD] 1 405,41 [/TD]
[TD] 2 [/TD]
[TD] 2 248,66 [/TD]
[TD] 2 248,33 [/TD]
[/TR]
[TR]
[TD="align: right"]2018/03/30[/TD]
[TD="align: right"]2018/03/28[/TD]
[TD] 2 [/TD]
[TD] 0,80 [/TD]
[TD] 1,14 [/TD]
[TD]- 100,00 [/TD]
[TD] 1 305,41 [/TD]
[TD] 2 [/TD]
[TD] 2 088,66 [/TD]
[TD] 2 168,66 [/TD]
[/TR]
[TR]
[TD="align: right"]2018/04/09[/TD]
[TD="align: right"]2018/03/30[/TD]
[TD] 10 [/TD]
[TD] 0,80 [/TD]
[TD] 1,15 [/TD]
[TD]- 2,80 [/TD]
[TD] 1 302,62 [/TD]
[TD] 10 [/TD]
[TD] 10 420,92 [/TD]
[TD] 10 441,04 [/TD]
[/TR]
[TR]
[TD="align: right"]2018/04/11[/TD]
[TD="align: right"]2018/04/09[/TD]
[TD] 2 [/TD]
[TD] 0,80 [/TD]
[TD] 1,15 [/TD]
[TD]- 33,42 [/TD]
[TD] 1 269,20 [/TD]
[TD] 2 [/TD]
[TD] 2 030,71 [/TD]
[TD] 2 057,45 [/TD]
[/TR]
[TR]
[TD="align: right"]2018/04/11[/TD]
[TD="align: right"]2018/04/11[/TD]
[TD] - [/TD]
[TD] 0,80 [/TD]
[TD] 1,15 [/TD]
[TD]- 33,36 [/TD]
[TD] 1 235,84 [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD="align: right"]2018/04/11[/TD]
[TD="align: right"]2018/04/11[/TD]
[TD] - [/TD]
[TD] 0,80 [/TD]
[TD] 1,15 [/TD]
[TD]- 34,08 [/TD]
[TD] 1 201,76 [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD="align: right"]2018/04/11[/TD]
[TD="align: right"]2018/04/11[/TD]
[TD] - [/TD]
[TD] 0,80 [/TD]
[TD] 1,15 [/TD]
[TD]- 34,26 [/TD]
[TD] 1 167,50 [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD="align: right"]2018/04/11[/TD]
[TD="align: right"]2018/04/11[/TD]
[TD] - [/TD]
[TD] 0,80 [/TD]
[TD] 1,15 [/TD]
[TD]- 33,14 [/TD]
[TD] 1 134,36 [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD="align: right"]2018/04/11[/TD]
[TD="align: right"]2018/04/11[/TD]
[TD] - [/TD]
[TD] 0,80 [/TD]
[TD] 1,15 [/TD]
[TD]- 34,44 [/TD]
[TD] 1 099,92 [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD="align: right"]2018/04/11[/TD]
[TD="align: right"]2018/04/11[/TD]
[TD] - [/TD]
[TD] 0,80 [/TD]
[TD] 1,15 [/TD]
[TD]- 33,86 [/TD]
[TD] 1 066,06 [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD="align: right"]2018/04/12[/TD]
[TD="align: right"]2018/04/11[/TD]
[TD] 1 [/TD]
[TD] 0,80 [/TD]
[TD] 1,15 [/TD]
[TD] 12,58 [/TD]
[TD] 1 078,64 [/TD]
[TD] 1 [/TD]
[TD] 862,91 [/TD]
[TD] 862,91 [/TD]
[/TR]
[TR]
[TD="align: right"]2018/04/12[/TD]
[TD="align: right"]2018/04/12[/TD]
[TD] - [/TD]
[TD] 0,80 [/TD]
[TD] 1,15 [/TD]
[TD] 100,00 [/TD]
[TD] 1 178,64 [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 80,00 [/TD]
[/TR]
[TR]
[TD="align: right"]2018/04/12[/TD]
[TD="align: right"]2018/04/12[/TD]
[TD] - [/TD]
[TD] 0,80 [/TD]
[TD] 1,15 [/TD]
[TD] 300,00 [/TD]
[TD] 1 478,64 [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 240,00 [/TD]
[/TR]
[TR]
[TD="align: right"]2018/04/16[/TD]
[TD="align: right"]2018/04/12[/TD]
[TD] 4 [/TD]
[TD] 0,80 [/TD]
[TD] 1,15 [/TD]
[TD] 17,42 [/TD]
[TD] 1 496,06 [/TD]
[TD] 4 [/TD]
[TD] 4 787,38 [/TD]
[TD] 4 745,58 [/TD]
[/TR]
[TR]
[TD="align: right"]2018/04/18[/TD]
[TD="align: right"]2018/04/16[/TD]
[TD] 2 [/TD]
[TD] 0,80 [/TD]
[TD] 1,15 [/TD]
[TD] 0,20 [/TD]
[TD] 1 496,26 [/TD]
[TD] 2 [/TD]
[TD] 2 394,01 [/TD]
[TD] 2 393,85 [/TD]
[/TR]
[TR]
[TD="align: right"]2018/04/18[/TD]
[TD="align: right"]2018/04/18[/TD]
[TD] - [/TD]
[TD] 0,80 [/TD]
[TD] 1,15 [/TD]
[TD]- 0,06 [/TD]
[TD] 1 496,20 [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD="align: right"]2018/04/19[/TD]
[TD="align: right"]2018/04/18[/TD]
[TD] 1 [/TD]
[TD] 0,80 [/TD]
[TD] 1,15 [/TD]
[TD]- 1 000,00 [/TD]
[TD] 496,20 [/TD]
[TD] 1 [/TD]
[TD] 396,96 [/TD]
[TD] 396,96 [/TD]
[/TR]
[TR]
[TD="align: right"]2018/04/20[/TD]
[TD="align: right"]2018/04/19[/TD]
[TD] 1 [/TD]
[TD] 0,80 [/TD]
[TD] 1,15 [/TD]
[TD]- 200,00 [/TD]
[TD] 296,20 [/TD]
[TD] 1 [/TD]
[TD] 236,96 [/TD]
[TD] 236,96 [/TD]
[/TR]
[TR]
[TD="align: right"]2018/04/26[/TD]
[TD="align: right"]2018/04/20[/TD]
[TD] 6 [/TD]
[TD] 0,80 [/TD]
[TD] 1,15 [/TD]
[TD] 0,19 [/TD]
[TD] 296,38 [/TD]
[TD] 6 [/TD]
[TD] 1 422,64 [/TD]
[TD] 1 421,90 [/TD]
[/TR]
[TR]
[TD="align: right"]2018/05/01[/TD]
[TD="align: right"]2018/04/26[/TD]
[TD] 5 [/TD]
[TD] 0,85 [/TD]
[TD] 1,15 [/TD]
[TD]- 0,66 [/TD]
[TD] 295,73 [/TD]
[TD] 5 [/TD]
[TD] 1 256,84 [/TD]
[TD] 1 259,07 [/TD]
[/TR]
[TR]
[TD="align: right"]2018/05/03[/TD]
[TD="align: right"]2018/05/01[/TD]
[TD] 2 [/TD]
[TD] 0,85 [/TD]
[TD] 1,15 [/TD]
[TD] 20,00 [/TD]
[TD] 315,73 [/TD]
[TD] 2 [/TD]
[TD] 536,73 [/TD]
[TD] 519,73 [/TD]
[/TR]
[TR]
[TD="align: right"]2018/05/09[/TD]
[TD="align: right"]2018/05/03[/TD]
[TD] 6 [/TD]
[TD] 0,85 [/TD]
[TD] 1,15 [/TD]
[TD]- 215,59 [/TD]
[TD] 100,13 [/TD]
[TD] 6 [/TD]
[TD] 510,67 [/TD]
[TD] 1 426,95 [/TD]
[/TR]
[TR]
[TD="align: right"]2018/05/23[/TD]
[TD="align: right"]2018/05/09[/TD]
[TD] 14 [/TD]
[TD] 0,85 [/TD]
[TD] 1,15 [/TD]
[TD]- 30,00 [/TD]
[TD] 70,13 [/TD]
[TD] 14 [/TD]
[TD] 834,57 [/TD]
[TD] 1 166,07 [/TD]
[/TR]
[TR]
[TD="align: right"]2018/05/25[/TD]
[TD="align: right"]2018/05/23[/TD]
[TD] 2 [/TD]
[TD] 0,85 [/TD]
[TD] 1,15 [/TD]
[TD] 0,78 [/TD]
[TD] 70,91 [/TD]
[TD] 2 [/TD]
[TD] 120,55 [/TD]
[TD] 119,88 [/TD]
[/TR]
[TR]
[TD="align: right"]2018/05/31[/TD]
[TD="align: right"]2018/05/25[/TD]
[TD] 6 [/TD]
[TD] 0,85 [/TD]
[TD] 1,15 [/TD]
[TD]- 33,98 [/TD]
[TD] 36,93 [/TD]
[TD] 6 [/TD]
[TD] 188,34 [/TD]
[TD] 332,75 [/TD]
[/TR]
[TR]
[TD="align: right"]2018/06/04[/TD]
[TD="align: right"]2018/05/31[/TD]
[TD] 4 [/TD]
[TD] 0,85 [/TD]
[TD] 1,15 [/TD]
[TD]- 6,04 [/TD]
[TD] 30,89 [/TD]
[TD] 4 [/TD]
[TD] 105,03 [/TD]
[TD] 120,43 [/TD]
[/TR]
[TR]
[TD="align: right"]2018/06/06[/TD]
[TD="align: right"]2018/06/04[/TD]
[TD] 2 [/TD]
[TD] 0,85 [/TD]
[TD] 1,15 [/TD]
[TD]- 33,00 [/TD]
[TD]- 2,11[/TD]
[TD] 2 [/TD]
[TD] 52,52 [/TD]
[TD] 26,25 [/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

For the first line and using the provided data how is that calculated?