IF Statement

[mercadi]

Hello, I'm needing some help figuring out how to write this IF statement or if there is some other formula that will work better.

I have an EXCEL2000 worksheet that I need to prioritize the data. Let me explain.

SS# Order Priority AMT Default Desc
111-22-3333 99 9999 Y Checking
111-22-3333 97 50 N Savings
111-22-3333 98 110 N Savings

The chart above shows that this employee has three differant Direct Deposits. The AMT column shows the amount that goes into each account each payperiod. The dollar amount of the total paycheck varies from paycheck to paycheck so the $amount can be up to 9999. (Most folks only have one account)What I need to show is the priority for those that have more than one acct. The AMT is taking out from smallest to largest with the number 1, 2, or 3. So in this case it would look like this.

SS# Order Priority AMT Default Desc
111-22-3333 99 3 9999 Y Checking
111-22-3333 97 1 50 N Savings
111-22-3333 98 2 110 N Savings

 

[mintjulep]

If this is all you have, it can't be done.

Please tell us what else you know (i.e. the information needed to do the calculation), and what you have tried.

 

[mercadi]

This is pretty much it for the spreadsheet. I do have some other columns in the spreadsheet but nothing that would really help this. I just have over 3000 entries and they are wanting to know the priority level for each deposit on each employee. So if the employee has 2 or 3 differant accounts, lets say the make 1,000 on their pay check. They want $50 to go to one Savings acount, $100 to another savings account and the rest go into the primary checking account. What would happen on the accounting end, is they would take $50 out first, then the $100, then the remainder last (smallest amt to largest). The priority column has to show that by using 1, 2,or 3. I just couldn't figure out how to write a formula using their social security so the formula would know if the employee has either one account or 3 accounts. Does that make sense. Surely there is some way.......I REALLY don't have to have to do this manually for it would take sooooo long. I've looked at Priority IF statements but couldn't figure out how to make it work. Somehow the social has to be tied in I would think. Maybe someone can come up with something.

 

[dallen43]

If I understand correctly, you could sort the table then use a formula.

I would sort it by SS# and Amount.
Then I would place the following in the priority column (C in this example).

=IF(A2=A1,C1+1,1)

assuming the column A is SS# and column C is the priority.

If the file needs to be sorted in a different order, then change the results to values and resort to the order that it needs to be in.

Hope this helps,
Deb

 

[mercadi]

PERFECT, PERFECT, PERFECT!!!! Thank so much. I had to sort my AMT column, but it worked.