Free Microsoft Excel 2013 Quick Reference

Max function, excluding outliers

I have a list of unique identifiers in column A. Each unique identifier that has a number of dates associated with it in column B. I would like to calculate the max date for each unique identifier. However, I do not want it to deliver the oldest date as the max if it is an outlier. In this case if the max under the initial calculation is more than 180 days older then the next oldest date (it is considered an outlier) and I would it to return the second oldest value.

Unique ID Dates
aaa3546 3/1/08
aaa3546 4/16/08
aaa3546 12/31/08

The typical max function would return 12/31/08. Since there is more than 180 days between 12/31/08 and 4/16/08, I would like it to return 4/16/08. Any ideas would be appreciated.


Post your answer or comment

comments powered by Disqus
Uniform Issuance Analysis.xls

I'm trying to make sense of the uniform database at my company (it's a Japanese owned company, and they just love to wear uniforms). I want compile and analysis the latest issued uniform sizes and types to assist me in planning for new procurement quantity and stock-in-hand planning. I've managed to return the latest issue data at row N to Q. However, I want the formula to exclude the "Maternity" data and select the next non-"Maternity" latest data. I've tried to use the combination of IF and MAX function and insert it into the "Type" row, but to no avail. I would appreciate it very much is you guys could help me sort this problem out.

Formula for type row
=OFFSET(INDIRECT(CELL("address",INDEX(R6:AO6,MATCH(MAX(R6,V6,Z6,AD6,AH6,AL6),R6:AO6,0)))), 0, 1, 1, 1)

Formula for date issued
=MAX(R6,V6,Z6,AD6,AH6,AL6)

I have a file that has multiple account numbers. I'm trying to get the largest value of the code column (see attached) to align with the account number that has the patient count of "1". Is there a way to use the Max function to do this? Thanks for any help on this.

Hi.

I have the following data:

John - 200
Leah - 620
Phil - 540
Jackie - 540
Annie - 400

I am planning on doing a "Highest Earner" box in which I want the MAX function to return both the max earnings (620) with the earners name (Leah).

Is this possible or do I have to use a different formula?

How can I use a function similar to the "max Function" using more than 30 numbers?

I've built up an excel worksheet that did some manipulation on a small data table (in excel). I now need to repeat this on a really large data set and so am trying to translate my excel formulas into a (series of) Access queries.

I'm having a problem with one particular one. My query expression is:


	VB:
	
Grantcont1: IiF([residual income]

I have searched for a Function excluding Fridays and found one by barry houdini for excluding Sundays :
If I have a date in A1 and want to add the number of workdays in B1, counting all days except Fridays as workdays:

=A1-WEEKDAY(A1,3)+INT(7/6*(B1+MIN(5,WEEKDAY(A1,3))))

format as date...How to replace sundays with Fridays?
Thanks in advanced.

-----------------------
Salah

Hi,
I have a spreadsheet and i am trying to use a min and a max function for 20,000 rows of data, when i do this, i get a #DIV/0! error, any ideas on why this is?? If i change the range to only include 10,000 rows it works fine. Here is my formula...

=MIN(B6:B20004)----doesnt work-

Thanks for any help in advance,

Matt

Hi I am currently setting up a spreadsheet for teachers at our school for GCSE marks.

Basically there are four areas where they get marked in and the scores from them, averaged, determine which exam to enter them in.

But some students may have say one set missing but still scored highly on the other areas meaning they should be in the higher band but with a missing mark will be entered into an easy exam!

as said using the MAX function will just give the highest scores, is there a way of incorporating this to take above scenario into account?

Any help is much appreciated!!

I would like to know what the cell reference is when a value is pulled from a list or array using the MIN and MAX functions.
For example, when I have a list of prices from different suppliers, I want to choose the lowest price. Instead of looking through the list of prices, I would like the best supplier name to be picked out.
I have the data arranged in this way:
Item 1 - Supplier 1 - Price 1 - Supplier 2 - Price 2 - Supplier 3 - Price 3
Item 2 - Supplier 1 - Price 1 - Supplier 2 - Price 2 - Supplier 3 - Price 3
Item 3 - Supplier 1 - Price 1 - Supplier 2 - Price 2 - Supplier 3 - Price 3
Item 4 - Supplier 1 - Price 1 - Supplier 2 - Price 2 - Supplier 3 - Price 3

Any suggestions or help would be most welcome.

Thank you.

Greetings!

In col A, I have labels. In col B, I have numbers.

After using the MAX function for the values in col B, how do I get the label
(text) from the cell in col A with the highest value in col B resulting from
the MAX function?

Thanks.

I'm trying to clean up some data that is rather dirty and could use some
help here. What I would like to do is deduplicate data but with certain
criteria. I have duplicate company names and each company has a value.
For all the same companies I'd like to just select or mark the entry
with the maximum number of users.

Her is a same of the data

COMPANY NAME USERS
Company A 1
Company A 7
Company A 3
Company B 15
Company B 86
Company B 2
Company B 2
Company B 51
Company C 3
Company C 5
Company C 11

COMPANY NAME is column a and USER values is in column b. I'd like to
return the MAX USER values in column c

I would like the output to look like:
COMPANY NAME MAX USERS
Company A 7
Company B 86
Company C 11

I've tried to combine the IF formula and MAX but I need to somehow
create a scenario where the MAX function is executed only on the array
of formulas for the similar company names. Maybe hold it in an array -
which I don't know how to do.Maybe VB or VBA could solve this?

Here is the formula in theory:
=if(and(a3=a4), MAX: **-the range of values for each similar company
would go here** -, max value, delete))

ANy suggestions would greatly be appreciated.

Eddy

--
yo eddy
------------------------------------------------------------------------
yo eddy's Profile: http://www.excelforum.com/member.php...o&userid=35243
View this thread: http://www.excelforum.com/showthread...hreadid=550170

I'd like to return the maximum value for every 12th cell
in a very long column of values. i.e. MAX
(BC2,BC14,BC26,BC38,BC50....) Can I do this using the MAX
function without listing each individual cell reference in
the function argument? If not, is there another way to do
this?

Thanks much,
John

I have a spreadsheet with 60 columns and 1500 rows of data. For each row I need to idenitify the column with the maximum value (I used the MAX function and it worked fine). My problem is, I need to identify the column that contains the maximum value. For example: MAX(g2:bv2) max value is 5.7 belonging to column L2. Each column has a distinct column heading. Any suggestions?

The title is a bit off of what I'm trying to do, it's hard to explain.

What I'm trying to do is get the MAX function to return a value, but that value needs to come before the values reach zero. It's a cubic equation that basically starts, at the beginning of the data range, under 10 then hits zero and ends up near 30 at the end of the data range. The end data range max doesn't matter to me if the data range hits zero along the way.

Is there any way to make the MAX function stop at a certain value, or is there another function that could accomplish what I'm trying to do?

(Using Excel 2007).

If I have 2 columns and need to find the maximum value of the products of the 2 columns, is there a way of doing this without simply adding a third column for the product and using the MAX function on this third column?

I also cannot use =MAX(a1*b1,a2*b2,.......) as I have more than 30 rows in each column.

I'm using Excel 2003 and have a problem with the max function...
I'm trying to get the maximum of a range, but the problem is there are #N/A in some of the cells in the range. I need these to stay in as I use the same data to plot graphs.
For a small range I get the right answer using the array formula =MAX(IF(NOT(ISNA(T22:T30)),T22:T30)) but the range I need to check actually has more sections: eg. T35:T41,T48:T50,T60:T68 and when trying this range the formula doesn't work.
I have also tried naming the range and just putting the name into the formula above, and although it recognises the range I still get the #N/A result.
Any ideas?

I have a spreadsheet that i am pasting the results of Access queries in to.

I am using the MAX function to find the maximum of one of the colums (obviously). My problem is that the queries i run all have a different number of rows in their results, and as such i have to alter my MAX query every time (i'm also doing the same for MODE and MEAN).

For example, one query will have 12 rows so my MAX function will look like this:

=MAX(O2:O12)

But the next query may have 2548 rows, so my MAX function would need to look like this

=MAX(O2:O2548)

Is there anyway to get the function to automatically change to the second formula (or the correct formula for the number of rows i paste in) after i have pasted in the results?

As a result of another function i do have a cell that contains the number of rows i have pasted in, so for example for the first set of results as above the cell would contain 12, and for the second query it would contain 2548.

Thanks

Hi Guys,

I will really appreciate you help. I have been breaking my head for an hour or so to figure this out. But no luck!

Here is what i am trying to do

=IF(G6="",0,IF(G6="FFV",-1000,-(IF(G6="C",(6.5-E55)*1000+1000,IF(G6="V",(8.3-E55)*1000+1000)))))

In G6 we mannualy input depending on the following category.

I have 3 categories Cars (C), Vans (V) and FFV i want to perform the above calculation and if the result is greater than $2000 then the function should return max $2000 as the output.

I tried using the max function as follows
=IF(G6="",0,IF(G6="FFV",-1000,IF(MAX(-2000),-2000,-(IF(G6="C",(6.5-E55)*1000+1000,IF(G6="V",(8.3-E55)*1000+1000))))))

But i keep getting -2000 as output even when i change values in cell E55.

Let me know if further clarification is required.

Thanks in advance.

- Aamil

Dear Excel Forum,
I was trying to use the max function to extract information from a table, however for text cells, that function does not seem to work, please advice.
I am attaching the file for easy reference.

Pio Desousa Proenca
Dubai

Hi,

I currently use the following sumproduct formula to give me the monthly sales turnover, you will see that it includes various criteria to be met.

=SUMPRODUCT((E18:E1501)*(J18:J1501<>"paid acc.")*(J18:J1501<>"REFUND")) Is it possible to use the MAX function across all twelve of my worksheets which matches the criteria of the above formula i.e. not to include invoices which state 'Paid Acc.' and not to include invoices which state 'Refund'

Basically I am trying to find the highest invoice amount over the year.

Any advice would be grateful.

Hi all,

I've got a database with data per hour. The data is not regular as sometimes the data start at 7:00 and finish at 17:00 but in other ocassions is different.

I tried with sumproduct using the max function to get the last hour for a specific date but doesn't work. Please help.

I am trying to maximize a column in excel. I want to get netxt to that result
the month in in wich this maximum turnover has been achieved

january 50.000
february 60.000
march 70.000

I know how to use the max function, but I do not want just as a result
70.000, but I want to see next to it that this result comes from the month
march.
Please help
--
jeroen

I'm trying to clean up some data that is rather dirty and could use some help here. What I would like to do is deduplicate data but with certain criteria. I have duplicate company names and each company has a value. For all the same companies I'd like to just select or mark the entry with the maximum number of users.

Her is a same of the data

COMPANY NAME USERS
Company A 1
Company A 7
Company A 3
Company B 15
Company B 86
Company B 2
Company B 2
Company B 51
Company C 3
Company C 5
Company C 11

COMPANY NAME is column a and USER values is in column b. I'd like to return the MAX USER values in column c

I would like the output to look like:
COMPANY NAME MAX USERS
Company A 7
Company B 86
Company C 11

I've tried to combine the IF formula and MAX but I need to somehow create a scenario where the MAX function is executed only on the array of formulas for the similar company names. Maybe hold it in an array - which I don't know how to do.Maybe VB or VBA could solve this?

Here is the formula in theory:
=if(and(a3=a4), MAX: **the range of values for each similar company would go here** , max value, delete))

ANy suggestions would greatly be appreciated.

Eddy

I am trying to use the "MAX" function to find the max temp on a particular day in a week from 7 separate sheets. The problem I have is one particular company autopopulates their temperature block with the temp and degree symbol: 46°F instead of just 46. The max function is thrown off it appears by the symbol and letter. Is there a way to get the max function to only look at the numbers? I'm also open to the option of autoconverting each temp to a number on the master sheet and letting the "MAX" function search through those if that's possible. Thanks in advance.


No luck finding an answer? You could always try Google.