EXCEL MAXIMUM VALUE AND VLOOKUP NESTED?
Hello,
I have a question I am hoping someone in here may be able to help me with: Say I have a range of data that looks like this: A B 0.1 0.6 0.2 0.9 0.3 1.4 0.4 1.0 0.5 0.6 0.6 1.0 0.7 1.3 0.8 1.1 0.9 0.9 1.0 0.4 If I want to obtain the maximum value in column B for a range of 2 numbers correcponding to column A, which formula should be used? e.g. For values between 0.4 and 0.8 corresponding to A, the max in the range in column B is 1.3. Any help would be very appreciated. Thanks, Edu |
EXCEL MAXIMUM VALUE AND VLOOKUP NESTED?
Edu,
use the following *array* formula (commit with Shift+Ctrl+Enter) =MAX(IF((A1:A10=0.2)*(A1:A10<=0.4),B1:B10)) HTH Kostis Vezerides On Jul 3, 4:36 pm, Edu wrote: Hello, I have a question I am hoping someone in here may be able to help me with: Say I have a range of data that looks like this: A B 0.1 0.6 0.2 0.9 0.3 1.4 0.4 1.0 0.5 0.6 0.6 1.0 0.7 1.3 0.8 1.1 0.9 0.9 1.0 0.4 If I want to obtain the maximum value in column B for a range of 2 numbers correcponding to column A, which formula should be used? e.g. For values between 0.4 and 0.8 corresponding to A, the max in the range in column B is 1.3. Any help would be very appreciated. Thanks, Edu |
EXCEL MAXIMUM VALUE AND VLOOKUP NESTED?
assuming on..
A1 = min_search_value = 0.4 A2 = min_search_value = 0.8 A3:B12 contains your sample data.. then you may try something like this...on B1 array formula with CSE {=MAX(IF((A3:A12=A1)*(A3:A12<=A2),B3:B12))} regards, driller -- ***** birds of the same feather flock together.. "Edu" wrote: Hello, I have a question I am hoping someone in here may be able to help me with: Say I have a range of data that looks like this: A B 0.1 0.6 0.2 0.9 0.3 1.4 0.4 1.0 0.5 0.6 0.6 1.0 0.7 1.3 0.8 1.1 0.9 0.9 1.0 0.4 If I want to obtain the maximum value in column B for a range of 2 numbers correcponding to column A, which formula should be used? e.g. For values between 0.4 and 0.8 corresponding to A, the max in the range in column B is 1.3. Any help would be very appreciated. Thanks, Edu |
EXCEL MAXIMUM VALUE AND VLOOKUP NESTED?
Kostis, Driller,
This is exactly what I was after. Thanks a million. Cheers, Edu |
EXCEL MAXIMUM VALUE AND VLOOKUP NESTED?
=SUMPRODUCT(MAX((A1:A10=0.4)*(A1:A10<=0.8)*B1:B10 ))
"Edu" wrote: Hello, I have a question I am hoping someone in here may be able to help me with: Say I have a range of data that looks like this: A B 0.1 0.6 0.2 0.9 0.3 1.4 0.4 1.0 0.5 0.6 0.6 1.0 0.7 1.3 0.8 1.1 0.9 0.9 1.0 0.4 If I want to obtain the maximum value in column B for a range of 2 numbers correcponding to column A, which formula should be used? e.g. For values between 0.4 and 0.8 corresponding to A, the max in the range in column B is 1.3. Any help would be very appreciated. Thanks, Edu |
EXCEL MAXIMUM VALUE AND VLOOKUP NESTED?
maybe not this *array* formula with CSE
{=MAX((A3:A12=A1)*(A3:A12<=A2)*(B3:B12))} -- ***** birds of the same feather flock together.. "Edu" wrote: Hello, I have a question I am hoping someone in here may be able to help me with: Say I have a range of data that looks like this: A B 0.1 0.6 0.2 0.9 0.3 1.4 0.4 1.0 0.5 0.6 0.6 1.0 0.7 1.3 0.8 1.1 0.9 0.9 1.0 0.4 If I want to obtain the maximum value in column B for a range of 2 numbers correcponding to column A, which formula should be used? e.g. For values between 0.4 and 0.8 corresponding to A, the max in the range in column B is 1.3. Any help would be very appreciated. Thanks, Edu |
All times are GMT +1. The time now is 07:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com