Remove aging list if variance is zero

[rsharma017]

I have been put in charge of an inventory of AR items. Please see below

STATUS Acct. Number MS4 Bat # Date In (Column E) AGING (Column F) Date Out Amount In Amount Out Variance
Refunded 02/03/17 91+ 3/5/2017 56.83 (56.83)

i entered a formula =IF(TODAY()-E55<=30,"0-30",IF(TODAY()-E55<=60,"31-60",IF(TODAY()-E55<=90,"61-90","91+"))) to get the agin of a transaction. However I run pivots to provide update to management. The transaction shown has a zero balance. The pivot counts the 91+ item even though it has a zero balance. Couple of questions

1. is there a way to remove the 91+ when the transaction has a zero variance which is column J.

i am in a bind here and any help would be greatly appreciated.

I did come up with this formula though but could not figure out how to include variances that are less than zero as well.

<code>=if(j4>0,if(today()-e4<=30,"0-30",if(today()-e4<=60,"31-60",if(today()-e4<=90,"61-90","91+"))),"")</code>

Rahul S.

 

[rsharma017]

<colgroup><col style="mso-width-source:userset;mso-width-alt:5120;width:105pt" width="140"> <col style="mso-width-source:userset;mso-width-alt:6875;width:141pt" width="188"> <col style="mso-width-source:userset;mso-width-alt:4827;width:99pt" width="132"> <col style="mso-width-source:userset;mso-width-alt:5083;width:104pt" width="139"> <col style="mso-width-source:userset;mso-width-alt:3035; width:62pt" span="2" width="83"> <col style="mso-width-source:userset;mso-width-alt:4022;width:83pt" width="110"> <col style="mso-width-source:userset;mso-width-alt:5193;width:107pt" width="142"> <col style="mso-width-source:userset;mso-width-alt:4242;width:87pt" width="116"> <col style="mso-width-source:userset;mso-width-alt:4169;width:86pt" width="114"> </colgroup><tbody>
[TD="class: xl72, width: 140"]Month[/TD]
[TD="class: xl73, width: 188"]STATUS[/TD]
[TD="class: xl72, width: 132"]Acct. Number[/TD]
[TD="class: xl74, width: 139"]MS4 Bat #[/TD]
[TD="class: xl75, width: 83"]Date In[/TD]
[TD="class: xl76, width: 83"]AGING[/TD]
[TD="class: xl77, width: 110"]Date Out[/TD]
[TD="class: xl78, width: 142"]Amount In[/TD]
[TD="class: xl78, width: 116"]Amount Out[/TD]
[TD="class: xl78, width: 114"]Variance[/TD]

[TD="class: xl80, width: 140"]JAN 17[/TD]
[TD="class: xl81, width: 188"][/TD]
[TD="class: xl82"][/TD]
[TD="class: xl83, width: 139"][/TD]
[TD="class: xl84, width: 83"][/TD]
[TD="class: xl84, width: 83"][/TD]
[TD="class: xl85, width: 110"][/TD]
[TD="class: xl86, width: 142"][/TD]
[TD="class: xl86, width: 116"][/TD]
[TD="class: xl87, width: 114"] [/TD]

[TD="class: xl89, width: 140"]JAN 17[/TD]
[TD="class: xl90, width: 188"]Refunded[/TD]
[TD="class: xl91"][/TD]
[TD="class: xl89, width: 139"][/TD]
[TD="class: xl84, width: 83"]01/03/17[/TD]
[TD="class: xl92"]91+[/TD]
[TD="class: xl85, width: 110"]1/29/2017[/TD]
[TD="class: xl86, width: 142"]5,550.00 [/TD]
[TD="class: xl86, width: 116"] (5,550.00) [/TD]
[TD="class: xl87, width: 114"] [/TD]

[TD="class: xl89, width: 140"]JAN 17[/TD]
[TD="class: xl90, width: 188"]Refunded[/TD]
[TD="class: xl91"][/TD]
[TD="class: xl89, width: 139"][/TD]
[TD="class: xl84, width: 83"]01/03/17[/TD]
[TD="class: xl92"]91+[/TD]
[TD="class: xl85, width: 110"]1/29/2017[/TD]
[TD="class: xl86, width: 142"]5,550.00 [/TD]
[TD="class: xl86, width: 116"] (5,550.00) [/TD]
[TD="class: xl87, width: 114"] [/TD]

[TD="class: xl89, width: 140"]JAN 17[/TD]
[TD="class: xl90, width: 188"]Refunded[/TD]
[TD="class: xl91"][/TD]
[TD="class: xl89, width: 139"][/TD]
[TD="class: xl84, width: 83"]01/03/17[/TD]
[TD="class: xl92"]91+[/TD]
[TD="class: xl85, width: 110"]1/29/2017[/TD]
[TD="class: xl86, width: 142"]5,813.53 [/TD]
[TD="class: xl86, width: 116"] (5,813.53) [/TD]
[TD="class: xl87, width: 114"] [/TD]

[TD="class: xl89, width: 140"]JAN 17[/TD]
[TD="class: xl90, width: 188"]Refunded[/TD]
[TD="class: xl91"][/TD]
[TD="class: xl89, width: 139"][/TD]
[TD="class: xl84, width: 83"]01/03/17[/TD]
[TD="class: xl92"]91+[/TD]
[TD="class: xl85, width: 110"]1/29/2017[/TD]
[TD="class: xl86, width: 142"]2,700.00 [/TD]
[TD="class: xl86, width: 116"] (2,700.00) [/TD]
[TD="class: xl87, width: 114"] [/TD]

[TD="class: xl89, width: 140"]JAN 17[/TD]
[TD="class: xl90, width: 188"]Refunded[/TD]
[TD="class: xl91"][/TD]
[TD="class: xl89, width: 139"][/TD]
[TD="class: xl84, width: 83"]01/03/17[/TD]
[TD="class: xl92"]91+[/TD]
[TD="class: xl85, width: 110"]1/29/2017[/TD]
[TD="class: xl86, width: 142"]105.71 [/TD]
[TD="class: xl86, width: 116"] (105.71) [/TD]
[TD="class: xl87, width: 114"] [/TD]

[TD="class: xl89, width: 140"]JAN 17[/TD]
[TD="class: xl90, width: 188"]Researching[/TD]
[TD="class: xl91"][/TD]
[TD="class: xl89, width: 139"][/TD]
[TD="class: xl84, width: 83"]01/03/17[/TD]
[TD="class: xl92"]91+[/TD]
[TD="class: xl85, width: 110"]1/29/2017[/TD]
[TD="class: xl86, width: 142"] (105.71) [/TD]
[TD="class: xl86, width: 116"][/TD]
[TD="class: xl87, width: 114"] (105.71) [/TD]

</tbody>