Advanced IF function Help

**dhyun3** · 10-02-2012, 11:44 AM

Hi all,

I have a list of accounts (1000+) and have to do some data manipulation to make sure that the accounts have enough fees. It's fairly simple task, basically comparing the estimated fees and current cash balance. And if the current cash balance is greater than the estimated fees, we don't really do much. If not, we will generate more cash in the account to cover the fees.

However, we have some accounts that pay fees for other accounts as well. I was wondering if there is a way to make this simple that I can easily see which accounts will need to raise cash.
Please see attached.

Thanks in advance for your help!

**NBVC** · 10-02-2012, 11:52 AM

we have some accounts that pay fees for other accounts as well

how will Excel know this?

**dhyun3** · 10-02-2012, 12:12 PM

Because I have account number as well as billing account number in the same row. If the two accounts number are the same, we know that.

**FDibbins** · 10-02-2012, 12:08 PM

if all you want to do is identify accounts that have less money in them than "estimated fees", have you tried playing around with conditional formatting to change the color of the delinquent cells?

And if the current cash balance is greater than the estimated fees, we don't really do much. If not, we will generate more cash in the account to cover the fees.

**dhyun3** · 10-02-2012, 12:13 PM

I have tried that way. It's very easy to estimate whether the account has enough for fees when the account and the billing account are the same. However, it gets trickier when an account also pays the other accounts as well.
Please see the attached file.

**FDibbins** · 10-04-2012, 08:31 PM

your attachment doesnt show (that i can see) what/how other accounts are paid.

try this tho. highlight the entire range, select conditional formatting, select "formula" and enter this...

=$E2<=$D2