Hello,
I have a situation that I'm looking for help on a formula that seems to be stumping me. The data that I'm working with are members accounts but what I'm specifically needing to pull is members money market accounts which would be referenced as Item #'s 7, 8, and 10. The two columns of data that I'm working with are of course Item # and Balance. Now the way we have a program set up is we are breaking these members money market accounts into tiers based on their balance. Those tiers are as follows:
Tier 1.1 - Less than 2,500
Tier 1.2 - 2,501 - 25,000
Tier 1.3 - 25,001 - 50,000
Tier 1.4 - 50,001 - 100,000
Tier 1.5 - Greater than 100,000
So this seems pretty easy up front but here is where the problem occurs. For some reason the data that is being pulled over from our system is classifying these money market accounts as item #'s 7, 8, and 10. The program is only able to look at item #'s to which it will then put the corresponding item # in it's line item (tier breakdowns) of our financials. So what I need to find a formula for is a way to look for the item #'s above and when the formula finds the item # to then look at the balance column. Once it looks at the balance column I need it to return the tier number that the balance would fall into. For example if it finds a item # 10 and the balance of that item is $5,000 I need the cell to populate "1.2".
Now I know that a pivot table could probably be used here but the file we are using is imported into a software that cannot look at tables. It can only look at one column of data. So that is why I need to be able to have tier numbers returned which I will then replace the Item # with the tier number. I have done something similar with IF AND formula's but I didn't have the multiple tiers like I have here.
Thanks in advance!
I have a situation that I'm looking for help on a formula that seems to be stumping me. The data that I'm working with are members accounts but what I'm specifically needing to pull is members money market accounts which would be referenced as Item #'s 7, 8, and 10. The two columns of data that I'm working with are of course Item # and Balance. Now the way we have a program set up is we are breaking these members money market accounts into tiers based on their balance. Those tiers are as follows:
Tier 1.1 - Less than 2,500
Tier 1.2 - 2,501 - 25,000
Tier 1.3 - 25,001 - 50,000
Tier 1.4 - 50,001 - 100,000
Tier 1.5 - Greater than 100,000
So this seems pretty easy up front but here is where the problem occurs. For some reason the data that is being pulled over from our system is classifying these money market accounts as item #'s 7, 8, and 10. The program is only able to look at item #'s to which it will then put the corresponding item # in it's line item (tier breakdowns) of our financials. So what I need to find a formula for is a way to look for the item #'s above and when the formula finds the item # to then look at the balance column. Once it looks at the balance column I need it to return the tier number that the balance would fall into. For example if it finds a item # 10 and the balance of that item is $5,000 I need the cell to populate "1.2".
Now I know that a pivot table could probably be used here but the file we are using is imported into a software that cannot look at tables. It can only look at one column of data. So that is why I need to be able to have tier numbers returned which I will then replace the Item # with the tier number. I have done something similar with IF AND formula's but I didn't have the multiple tiers like I have here.
Thanks in advance!
Last edited:
Make sure you have that right.
