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.

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.

- Excluding certain value from max function that has multiple separated values
- Conditional MAX Function
- Max Function To Return Words & Figures
- Max Function With More Than 30 Ranges
- Error with MAX function in Access expression
- WORKDAY Function excluding Fridays
- Is there a limit for min/max functions???
- Obtaining highest number - incorporating MAX function
- Finding cell reference with MIN or MAX functions
- Find the Cell Reference Resulting from MAX function
- IF formula with MAX function
- Using the MAX function with "constant increment" cell references
- MAX Function and Corresponding Column
- MAX Function limit?
- Max function on product of 2 cols?
- MAX function for split range
- MAX Function - Changing Range
- IF and Max Function
- Max function not working for text cells
- MAX function across worksheets with various arrays
- Get the last hour for a specific date using sumproduct and max functions
- Extra results in the MAX function
- IF formula with MAX function
- Can you use MAX function on numbers/text?

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 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?

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

VB:Grantcont1: IiF([residual income]

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

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

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!!

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.

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.

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

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

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).

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

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 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

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

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

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.

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.

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

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

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