how to do Multiple IFS within multiple IFS

[tommystanek]

My lack of understanding of excel functions and formulas may have me going down the wrong path possibly even leading to the wrong question. So here it is:

I have 4 salesman

I have rows for each salesman and columns for each month of the year and within the cells are the totals sales of that salesman for that month.

On the top of the sheet I want it to identify the salesman that made the most sales for the current month (self updating as the months change throughout the year)

So what i've done is :

Sheet33!B3 Cell identifies the month of the year numerically. (example, Jan=1, Feb=2, March=3, etc..)

L1 Cell identifies the highest dollars sold for the current month of the salesman =IF(Sheet33!B3=1,(MAX(B45,B55,B63,B87)),IF(Sheet33!B3=2,(MAX(C45,C55,C63,C87)),IF(Sheet33!B3=3,(MAX(D45,D55,D63,D87)),IF(Sheet33!B3=4,(MAX(E45,E55,E63,E87)),IF(Sheet33!B3=5,(MAX(F45,F55,F63,F87)),IF(Sheet33!B3=6,(MAX(G45,G55,G63,G87)),IF(Sheet33!B3=7,(MAX(H45,H55,H63,H87)),IF(Sheet33!B3=8,(MAX(I45,I55,I63,I87)),IF(Sheet33!B3=9,(MAX(J45,J55,J63,J87)),IF(Sheet33!B3=10,(MAX(K45,K55,K63,K87)),IF(Sheet33!B3=11,(MAX(L45,L55,L63,L87)),IF(Sheet33!B3=12,(MAX(M45,M55,M63,M87)),("tommy help!")))))))))))))

Now I'm trying to ask excel to first look at Sheet33!B3 to know what month it is
Then I'm asking it to check against the data for that month, if the sales equal the max sold identified in L1 to display the salesman who made those sales. Example:
IF Sheet33!B3=5, and IF L1=F45 then display "Lucas", otherwise IF L1=F55 then display "John", otherwise IF L1=F63 then display "Aaron", otherwise IF L1=F87 then display "T&M",
OTHERWISE IF Sheet33!B3=6, and IF...the formula written on the previous line would repeat itself for every month of the year.

The formula i though would get me what I need i wrote as follows (this is only written to see if i can get it to work for months 5,6&7):
=IF(Sheet33!B3=5,IF($L$1=F45,"LUCAS",IF(Sheet33!B3=5,IF($L$1=F55,"JOHN",IF(Sheet33!B3=5,IF($L$1=F63,"AARON",IF(Sheet33!B3=5,IF($L$1=F87,"T&M",),IF(Sheet33!B3=6,IF($L$1=G45,"LUCAS",IF(Sheet33!B3=6,IF($L$1=G55,"JOHN",IF(Sheet33!B3=6,IF($L$1=G63,"AARON",IF(Sheet33!B3=6,IF($L$1=G87,"T&M",),IF(Sheet33!B3=7,IF($L$1=H45,"LUCAS",IF(Sheet33!B3=7,IF($L$1=H55,"JOHN",IF(Sheet33!B3=7,IF($L$1=H63,"AARON",IF(Sheet33!B3=7,IF($L$1=H87,"T&M",),("tommy help!"))))))))))))))))))))))

After I get it working, I would copy, paste and adjust accordingly to include all the months of the year.

Unfortunately what I'm doing is getting me a FALSE response.

Help?

Thanks in advance!

[AliGW]

It's pretty likely that there will be a more efficient way of doing this.

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[davsth]

You really need to post an attachment, but i'm sure it can be simplified

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[tommystanek]

I have uploaded a sample sheet as suggested.

Simply stated, I'm just trying to get cell G3 to display the name of the salesman that made the most sales for the current month (and as the months change, so would the name displayed here)

Thank you again in advance!

-t

[MrShorty]

Here's how I would probably formulate this:

1) One trick about the INDEX() function is that if either the row or column # argument is 0, it will return the entire column/row. So, INDEX($B$7:$M$10,0,$B$3) will return the entire column for June (as long as B3 returns 6 and the months are in order left to right in the table like your example shows). INDEX() help file: https://support.office.com/en-us/art...2-b56b061328bd
2) Nest that inside of the MAX() function to get the maximum value =MAX(INDEX(...))
3) A MATCH() function to determine which row contains the max value =MATCH(MAX(INDEX(...)),INDEX(...),0)
4) Finally, an INDEX() function to return the salesman's name from column A =INDEX($A$7:$A$10,MATCH(...))

Will something like that work for you?

Edit to add: I find that a lot of "how to deal with multiple nested IF() functions" questions are often more easily handled using Excel's lookup functions. You may spend some time familiarizing yourself with these functions: https://support.office.com/en-us/art...7-3ec89df2b23e

[davsth]

not withstanding its volatile, I was along similar lines

=INDEX(A7:A10,MATCH(MAX(OFFSET($A$7,0,B3,4,1)),OFFSET($A$7,0,B3,4,1),0))

but would be better if column A just contained the names

[Bo_Ry]

Please try at G3
=TRIM(LEFT(SUBSTITUTE(INDEX(A:A,MOD(MAX(INDEX(INDEX(B7:M10,,B3)*10^5+ROW(A7:A10),)),1000))," ",REPT(" ",20)),20))

[tommystanek]

Thank you MrShorty!

I should have known that simplifying the data may not get me the result I need. I'll upload another sheet that is a better example of how the data I can work with is displayed (in between the total sold data for the salesman is more data like how many bids, total of the bids sent out, ect.)

I think that would prevent the INDEX function to work because it would be searching data that isn't related specifically to sales...right?

[davsth]

maybe find the value first entered as an array with shift ctrl enter

in b4 =MAX(IF(RIGHT(A7:A33,10)="sold total",OFFSET(A7:A33,0,B3),""))

then search for this value
=INDEX(A7:A37,MATCH(B4,OFFSET(A7:A37,0,B3),0))

[tommystanek]

davsth, can you modify the document i uploaded to show me how that would work?

[MrShorty]

I think that would prevent the INDEX function to work because it would be searching data that isn't related specifically to sales...right?

It does not prevent the INDEX() part of the algorithm from working, but the algorithm as formulated does not know which values correspond to sales and which correspond to other types. I would probably use the same sequence of functions, but replace the MAX() function with a MAXIFS() function. As davsth noted in post #6, this will be a lot easier if you separate name from value type in column A.

May I ask what the overall project looks like? This part looks to me like a "pivot table" based on a larger database. Part of me thinks that, if there is a good database behind this pivot table, then a second small pivot table could quickly and easily tell you who has the max sales for a given month. I don't work with databases, but it sometimes seems that a good database design can allow easier querying of the database for this kind of information.

Edit to add: I just noticed that your profile shows you using 2010, and the MAXIFS() function did not get introduced until 2016 (I think). You might be able to use MAX(IF(..)) type structure, or upgrade Excel, or use a different spreadsheet that has the MAXIFS() function.

[MrShorty]

(response to PM) I'm not sure that a phone call will help -- assuming you have understood the suggestions so far.

Google sheets has the MAXIFS() function, so this should be straightforward. I uploaded your sample sheet to my google account so I could work on it in sheets.

1) As noted, this will be a lot easier if name and record type are separated. I used the SPLIT() function (https://support.google.com/docs/answer/3094136?hl=en ) to separate name and type. In Q7, I entered =SPLIT(A7," ") This put the name in Q7, and the rest of the text in R7 and S7. Copied that down to the bottom of the table. I figure I can retrieve name from column Q and use "sold" in column R to find the values specific to sales.
2) In G1 (because it was convenient -- choose your own destination for the formulas), I entered the MAXIFS() function (https://support.google.com/docs/answer/7013817 ). =maxifs(index($B$7:$M$33,0,$B$3),$R$7:$R$33,"Sold"). This returns the maximum from the Jun column where column R contains "sold".
3) In G2, I entered my MATCH() function =match(G1,index($B$7:$M$33,0,$B$3),0)
4) In G3, I entered the final name function =index($Q$7:$Q$33,G2)

As one who like to spread calculations out over multiple cells (I find it easier to build and debug and edit formulas that way), I would leave it at that. If necessary, I'm sure those three steps could be nested into one giant formula.

Which of those formulas do you have trouble entering?

[tommystanek]

Got it! THANK YOU ALL!