Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to find the largest change of values in a column
I need to know if there is a way to find the largest change in a single
column of numbers, even if they are negative values. EXAMPLE: Column A1:A3 contains the following numbers 135.67 23.45 -256.77 I need to show the returned information in Cell B6. Every function I use returns 135.67 as the largest but I need to return the -256.77, (and show the negative sign). This is a very simple example but I hope someone can help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to find the largest change of values in a column
Ravager,
=MAX(A1:A3,ABS(MIN(A1:A3))) or array enter (use Ctrl-Shift-Enter) =MAX(ABS(A1:A3)) HTH, Bernie MS Excel MVP "Ravager" wrote in message ... I need to know if there is a way to find the largest change in a single column of numbers, even if they are negative values. EXAMPLE: Column A1:A3 contains the following numbers 135.67 23.45 -256.77 I need to show the returned information in Cell B6. Every function I use returns 135.67 as the largest but I need to return the -256.77, (and show the negative sign). This is a very simple example but I hope someone can help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to find the largest change of values in a column
Bernie Deitrick wrote...
Ravager, =MAX(A1:A3,ABS(MIN(A1:A3))) or array enter (use Ctrl-Shift-Enter) =MAX(ABS(A1:A3)) .... OP stated he needed the sign for negative results. Your formulas only return absolute values, so no signs. "Ravager" wrote in message I need to know if there is a way to find the largest change in a single column of numbers, even if they are negative values. .... Every function I use returns 135.67 as the largest but I need to return the -256.77, (and show the negative sign). This is a very simple example but I .... Try =LOOKUP(2,1/(A1:A3^2=MAX(A1:A3^2)),A1:A3) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to find the largest change of values in a column
Thanks, Harlan, I zoned on that.
=IF(MAX(A1:A3)ABS(MIN(A1:A3)),MAX(A1:A3),MIN(A1:A 3)) might be simpler for a novice to understand / maintain. Bernie MS Excel MVP OP stated he needed the sign for negative results. Your formulas only return absolute values, so no signs. "Ravager" wrote in message I need to know if there is a way to find the largest change in a single column of numbers, even if they are negative values. ... Every function I use returns 135.67 as the largest but I need to return the -256.77, (and show the negative sign). This is a very simple example but I ... Try =LOOKUP(2,1/(A1:A3^2=MAX(A1:A3^2)),A1:A3) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to find the largest change of values in a column
Bernie Thank You for the quick reply.
I had already tried a formula like that. It does find the largest value but doesn't return the negative sign as part of the answer. I need to show that the biggest change I had was a "Negative Value" - a loss. I am sure it is because of the ABS function. Thanks again "Bernie Deitrick" wrote: Ravager, =MAX(A1:A3,ABS(MIN(A1:A3))) or array enter (use Ctrl-Shift-Enter) =MAX(ABS(A1:A3)) HTH, Bernie MS Excel MVP "Ravager" wrote in message ... I need to know if there is a way to find the largest change in a single column of numbers, even if they are negative values. EXAMPLE: Column A1:A3 contains the following numbers 135.67 23.45 -256.77 I need to show the returned information in Cell B6. Every function I use returns 135.67 as the largest but I need to return the -256.77, (and show the negative sign). This is a very simple example but I hope someone can help. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to find the largest change of values in a column
Thanks Harlan and Bernie.
Both formulas worked perfectly. I can't believe how fast you were to answer and solve my problem. I have stared and experimented on this spreadsheet for 2 hours trying to get the correct answer and you guys did it in 5 minutes.....gee "Bernie Deitrick" wrote: Thanks, Harlan, I zoned on that. =IF(MAX(A1:A3)ABS(MIN(A1:A3)),MAX(A1:A3),MIN(A1:A 3)) might be simpler for a novice to understand / maintain. Bernie MS Excel MVP OP stated he needed the sign for negative results. Your formulas only return absolute values, so no signs. "Ravager" wrote in message I need to know if there is a way to find the largest change in a single column of numbers, even if they are negative values. ... Every function I use returns 135.67 as the largest but I need to return the -256.77, (and show the negative sign). This is a very simple example but I ... Try =LOOKUP(2,1/(A1:A3^2=MAX(A1:A3^2)),A1:A3) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Data in two seperate Spreadsheets | Excel Worksheet Functions | |||
Conditional Format as a MACRO | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Find values in a column | Excel Discussion (Misc queries) | |||
find the largest number in column | Excel Discussion (Misc queries) |