Figuring out multiple IF functions in this cell.

[Kcsimmons9]

Okay!

Firstly thanks for providing such a great site. There's a lot of knowledge here. Im very new to using excel, so I've used this site a lot to figure stuff out, but this is the first time I havent been able to find an answer. I'm sorry if this question has already been answered on another thread, I looked but I couldn't find anything. If it is on another thread, before you just refer me there can you check to see if its an extremely similar scenario, because like I said I'm new, and I may not be able to apply the concept to my personal formula.

Okay, so I simplified my example, but basically what we have here is one cell that I need to reflect 4 cells of data. In the four cells referenced, if the value is greater than or equal to a specific limit, I need a letter identifier to be placed in the one cell. So if the limit is reached in the first 2 cells, the one cell would say, "T, W". I have it figured out for 3 sources of data, but when I try to run 4 sources I reach my limit on the total number of nester IF's per formula. I'm using 2007, and while I realize there is a lot of data about bypassing the number of nested IF's I cant figure it out still. I also don't want to get into the VB scripting side. I want to handle all of my business in that cell. Here is what it looks like.


EXCEL.png


Please break it down as simply as you can make it. I'm not that smart. Thanks for any help guys, I really appreciate it.

Kyle

[dredwolf]

I do not know other members preferences, BUT, I suspect most of them are like me, and would like to see actual data to work on...no-one wants to recreate a spreadsheet from a screenshot, so if you upload a sample with a few examples of whats expected AND why it is expected, it would probably get you a quicker solution (We are kind of competitive here, so once there is something to work with, you will probably have several answers within a few hours )

[Kcsimmons9]

That sounds like a very good point. Here you go! Right now it is configured the way that works, for the first 3 cells of data. (A1, A2, and A3). As you can see as you change the data in those cells, if you go over the limits for each cell, (90 for A1, 40 for A2, and 6 for A3), the corresponding letter is placed into the display cell. Now I just need to throw cell A4 into the mix. The limit for that cell would be A4>=.05, and the letter would be P. Just a reminder, this needs to be able to display ANY possible configuration of these 4 letters, or none of them at all.


Example 1.xlsx

Thanks for the help!
Kyle

[dredwolf]

Well VBA is a separate forum, so if thats is what you actually want, ask a moderator to move it there for you, but as a formula ( and this is assuming 4 values ) you could use this (I'm using 0.05 as the next cut-off as well):

Please Login or Register  to view this content.

(In attached, this is in C2, C1 has the basic formula (without the trailing ',' removed))

Hope this helps

[Kcsimmons9]

Okay, so for some reason I've tried to reply multiple times and it keeps deleting my reply and not posting. I think it might be my internet. One more try.

Thanks for the help. You definitely steered me in the right direction, and from what you gave me I was able to create a product im comfortable with keeping. I just had one issue that just has to do with perfecting my vision. So for C2, you had a formula that removes the trailing comma. I LOVE that. But for some reason when none of criteria are met and that cell should be blank, it has an error. I assume it has something to do with the formula trying to remove characters that don't exist, but despite my best efforts I couldn't fix it. I don't understand how the LEFT and LEN work exactly. One final bit of help would be much appreciated!

[dredwolf]

try this :

Please Login or Register  to view this content.

The IFERROR will return a blank if the LEFT function returns an error

Hope this helps

[Kcsimmons9]

Problem solved! For now. :P Thanks for the help. I really do appreciate it. I'll change the thread to solved and I already gave you rep.

[dredwolf]

You are welcome, and Thanks for the rep hit, Much appreciated