IF AND dilemma

[herschen]

I've used PHP, but I'm not used to Excel for creating IF, AND, formulas. I work at a firm where we have several representatives who are licensed in specific states and in each state, there are different loans that we are assigned (based on how many representatives are licensed). Also in most states, there are no licensing guidelines, so everyone is eligible there.

I tried doing a simpler formula because I know that I got multiple IF's working when I was just working with the account numbers (not dealing with the licensing states), but I can't seem to get the AND to play nice with IF:

Please Login or Register  to view this content.

This works fine. If the number is below 8, it shows Arnold, below 15, Alan, and so on.

Please Login or Register  to view this content.

This one is supposed to show 'Alan' when the state is IL and the account # is 00-49, also show 'Alan' if the state is AL and the account # is 34-44 and do the same for GA. If it's not any of those, it should show 'Not.'

I'd like to eventually put every scenario in this formula. So if Bradshaw is licensed in OH and has accounts 35-40 there, I'd like his name to pop up.

Please let me know what I'm doing wrong. Thanks.

[Pete_UK]

You need to note that those account numbers are text values (unless formatted to show in the form "00-00"), so you can't compare J2 directly with the first two numbers - you would need to do something like this:

LEFT(J2,2)="00"

If you want to treat them as numbers so that you can compare a range (as in your second IF), then you could do this:

... IF((AND(LEFT(J2,2)+0>=34,LEFT(J2,2)+0<=44,G2="AL"), "Alan", ...

Adding a zero (or multiplying by 1) converts the text values into numbers.

Hope this helps.

Pete

[herschen]

Thanks Pete_UK. I already had that issue (and I believe it was resolved). I used the right function to pull out the last two digits but put the value function in front of it.

Please Login or Register  to view this content.

. There is something else with my code that I did wrong, but I can't figure it out.

[FDibbins]

I think if you are dealing with text 00, then it might be best to leave it as text.
Also, another way to "value" LEFT() is =--LEFT(A1,5)

I think it would be best if you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[Richard Buttrey]

Hi,

As Ford has suggested it would be best if you uploaded your workbook. It may be that you could use a Pivot Table to analyse your data and avoid IF tests - even if it means adding a helper column or two to your data.

[herschen]

Attachment 369774 I've resolved the initial question. However, the problem is that the true situation is that AL, AR, FL, GA, IL, LA, MA, MI, MN, MS, NM, OH, SC, TX, UT, VT, WI, WV all require licensing and there are more like 10 employees that are licensed. This means that there would be more than 64 arguments (and it would be a mess). How would I take this simple IF AND formula and convert it to a pivot table? Here's the code that worked:

Please Login or Register  to view this content.

[Pete_UK]

Change your table in T2:X7 so that it only has the starting numbers of the range, i.e.:

Alan........... ......0...... ........0
Arnold........0....34...... ......21
Bradshaw ..34....67..... .......41
Cole..........67...... .....0......61
David.......... ....... ..... .......81

(that might not align completely correctly), and then you can use this formula in H3 (I put it in I3 so I could compare against your names):

=INDEX($T$3:$T$7,MATCH(F3,INDEX($U$3:$X$7,0,IFERROR(MATCH(G3,$U$2:$W$2,0),4))))

then copy down. It gives a different result on row 7, but I think Arnold is the correct answer.

Hope this helps.

Pete

[Richard Buttrey]

Hi,

See attached.

The results in K3:K12 use the table called "tbl_states" on sheet1.