• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to use Large formula with conditions.

ThrottleWorks

ThrottleWorks

Excel Ninja
I have one file, this file has two tabs, in the 1st tab result will be populated, in the 2nd tab data is pasted.

Data in the 2nd tab is as below,


Column 1 - Date

Column 2 - Name of the person

Column - Amount


There are 4 such tables in the 2nd tab as per the region.

For example table 1 represents “North”, table 2 “East”, table 3 “South”, table4 “West”.


In the 1st tab I have date & region (both selected by user)


I want to calculate 5 large amount in the 1st tab according to the date & region selected.

For example if the user has selected 12 July 2012 & North, the large formula should give me large (1,2,3,4,5) amount according to it.

There might be a possibility that on 12 July we have 8 entries for north so the large formula will give me 5 max amounts.


I tried “If”, “And”, “Large” with /without arrays but I am not able to do it, can anyone please help me in this.
 
Hi sachinbizboy,


Are the dates same for all the four tables or they are diff. for each??


Regards,
 
Hi Faseeh Sir, dates will be different for almost each cells, i have a collated data of everyday transactions, so every cell will be having different cell. for example table 1 has 2700 records, for 12 July I have 7 records, I want to calculate Large for this table out of these 7 records, thanks lot for the help...
 
Hi Sachin ,


I am sure Faseeh will provide you the solution , but I suggest you try to look at it from the point of view of the LARGE function.


You agree that the LARGE function has to be used ; given this , and given its syntax :


=LARGE(array,k)


where k will obviously be ROW(A1) , so that when you copy it downwards , it becomes 2 , 3 , 4 and 5.


What is array going to be ? array has to be such that :


a) it selects the right data range

b) it satisfies the date criterion


Assume that your selected region is in a cell labelled Region_Selection ; this can take the values "East" , "West" , "North" and "South". Just give these names to the different regions which need to be selected , and use the construct =INDIRECT(Region_Selection) ; this will select the required data range.


Satisfying the date criterion is a matter of equating the first column of the data range , derived from the INDIRECT function , to the selected date ; this will return an array of TRUE / FALSE values ; multiplying this with the column of amounts will return an array of amounts. Use this as the first parameter of the LARGE function.


Narayan
 
Narayan Sir, thanks a lot for the reply however I am afraid I have not understand this, (my fault). Sir I have used a condition "if("A1" = "North",Large(B2:B2700,1)),"")


I am get the large on the base of region but not on the base of date. The forula I am using gives me large for the entire table, however I need Large fron the table1 for the selected date only, in our example we have 7 rows & I want large from these 7.


I am sorry but I am very poor at using indirect function, If you have time could you please explain how can I use this in our example.


I am not sure about how I will specify the date condition in the formula.


Thanks once again.
 
Hi, sachinbizboy!


Could you please detail exactly the structure of first and second sheets of your workbook?

I still don't get how the data is stored, you say you have 4 such tables, but you talk about a unique range B2:B2700.

Consider uploading a sample file.


Regards!
 
Hi Sachin ,


Let us start with the following data :

[pre]
Code: 
North	13
East	23
South	16
North	17
North	12
East	27
South	19
West	22
West	12
[/pre]
Suppose you name this range data_range.


Now , if you put in the formula =LARGE(data_range,1) you should get 27.


Now , if you use the following formula :


=LARGE((OFFSET(Data_range,0,1))*(OFFSET(Data_range,0,0)="North"),1)


you should get 17. To get this , of course , you have to enter it as an array formula , using CTRL SHIFT ENTER.


What the above formula is doing is , it is forming an array of TRUE / FALSE values :


OFFSET(Data_range,0,0) selects the first column of the range ; comparing the values in this column with "North" generates an array of values {TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE}


When we multiply this array of values with the data from the second column ( OFFSET(Data_range,0,1) ) , we get another array of numeric values {13;0;0;17;12;0;0;0;0} ; the zeroes result because FALSE is equivalent to 0 , and multiplication by 0 gives 0. TRUE is equivalent to 1 , and multiplying by 1 gives us the original amount from that column.


Narayan
 
Hi Narayan Sir, many many thanks for your valuable support. Have a very nice weekend. I must admit this is the best forum on excel.

Thanks for deriving some time from your busy schedule for me & explaining me the entire thing..
 
Hi Sachin ,


To continue from the earlier post :


So now , your task is to decide how to change the array which you will use , depending on the region which the user has selected.


Let us assume your region data are in the following ranges :

[pre]
Code: 
1.  East    :  A10:D24
2.  West    :  A30:D44
3.  North   :  A50:D64
4.  South   :  A60:D74
[/pre]
Let us assume the dates are in column A , and the amounts are in column D.


So now , if we could specify to the LARGE function to choose between A10:D44 and A30:D54 and so on , then we are done. One easy way to do this is to create named ranges ; create a range name East , and let it refer to $A$10:$D$24 ; similarly , create the other three ranges.


Let us assume the region selection is done in cell B5 ; this can be either typed in by the user , or selected through a drop-down.


If we use the construct =INDIRECT($B$5) , then if B5 contains "East" , the formula will refer to the range $A$10:$D$24.


Try the following :


You have already created a range named data_range for the data given in my earlier post.


Now , in cell B5 , type in the text data_range ; , put in the following formula :


=LARGE((OFFSET(INDIRECT($B$5),0,1))*(OFFSET(INDIRECT($B$5),0,0)="North"),1)


You should get the same result , 17 , as the earlier formula.


What we have done is , we have replaced a static reference data_range ( since it refers to a fixed range ) with a variable reference ; if you change the contents of B5 so that they point to a different range , then the same formula will give a different result.


This is exactly what we want ; we want that one cell B5 , if it contains "East" , should retrieve data from one particular range ; if the cell contains "West" , the same formula should retrieve data from a different range.


Narayan
 
Sir I am leaving for Home in next 15 minutes, I will work on it tommorow & share the results, once again many many thanks for the support & time..
 
Hi Narrayan!!!


Thanks for your kind words!! Am still working and hope to find something acceptable! :p


Hi Sachin!!


I am sorry i asked you to wait and i forgot i have to attend my MBA Evening Class, am really sorry for that.


Good Night Guys!

Faseeh
 
Hi Narrayan,


I worked out a formula that works fine when it comes to getting top five nos. from any region. That is by using your idea of listing ranges in cell and using indirect then to find the values.


Now i am stuck here: How will i retrive the row no. (or index no.) for top five values? Lets say for a certain category i have 1200 as the Largest value and the second value is also 1200. How will i identify that 1200 belongs to which row? Any clues??


The formula i used is:


Code: 
=LARGE(--INDIRECT("Sheet1!"&VLOOKUP(LookUpRegion,LookUpTable,ColOffset))*(--INDIRECT("Sheet1!"&VLOOKUP(LookUpRegion,LookupTable,ColOffset))=Date),ROW($A1))


Press Ctrl+Shift+Enter


Once row no. is identified we can easily retrieve corresponding name and date!


Thank You,

Faseeh
 
Hi Faseeh ,


I think you have gone in a slightly different direction !


From what I understood of Sachin's question , he has a column of dates ; corresponding to these dates , he has amounts in some other column , let us say , in the 4th column.


What is supposed to happen is that the user will select a particular region , by entering "North" , "South" , "East" or "West". Depending on this data entry , the correct table is supposed to be chosen ; these region tables need not necessarily have the names "North" , "South" ,... associated with them. Thus "North" may just refer to the range , let us say , A10:D22. In this range , column A will have the dates , and column D will have the amounts.


Given multiple dates which match the user-entered date , the top 5 amounts should be displayed.


I think this can be done without using the VLOOKUP function. Can you try this and let me know what you get ?

[pre]
Code: 
=LARGE((OFFSET(Selected_Table,0,0,ROWS(Selected_Table),1)=Selected_date)*OFFSET(Selected_Table,0,3,ROWS(Selected_Table),1),ROW(A1))
[/pre]
entered as an array formula , using CTRL SHIFT ENTER.


Here Selected_date is just one cell which contains the user-entered date. Selected_Table is a named range , which refers to : =INDIRECT(Sheet2!$F$1) where F1 contains the user-entered region , which can be one of "North" , "South" , "East" or "West". These are 4 named ranges referring to different areas within the worksheet , all of them having an identical structure ; the dates are in column A , and the amounts are in column D.


Narayan
 
Hi Narrayan,


Once again i am in class room and posting this in a break :). I will try your approach once i reach home, but i think it will work with orgional tables sorted for Amount, from largest to smallest. If it had been just choosing between four tables and fetching relavent values (sorted ones) then there was no issue with formula, obviously it will work!. Let me try it.


Thank You,

Faseeh
 
Hi Narayan Sir, Faseeh Sir, & SirJB7, thanks to you all for your help.

The amount of help & attention I have received is astonishing.

Extreamally sorry for the late reply, was very busy from last few days.


I was able to get the solution in following way..


=Large(if("column with date value = date,if(column of name = name,if(region value in the table = region,range for amount ())),1)


=large(if(a2:a50 = Date,if(b2:b50) = My name,if(c1 = North,d2:d50))),1)


I have converted this formula in array to get the results,


sorry for the poor description, thanks a lot once again.
 
I will definitely put the things more nicely,, very busy at the moment, extremely sorry for the poor description.


Have a nice day to everybody.
 
Hi, sachinbizboy!

Glad you could solved it. Thanks for your feedback and for your kind words too, but here all the credit goes for NARAYANK991 and Faseeh.

Welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top