Finding maximum value

Hello,

I want to find the maximum value in a column, but I want to ignore the
negative and positive part of the value. Basically, the maximum difference
from zero.

Example: the maximum value I am looking for will be -0.467.
-0.467
0.345
-0.253
0.411

Thanks
Ruan


0
ruan (28)
4/26/2004 5:43:24 AM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
359 Views

Similar Articles

[PageSpeed] 20

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

    =INDEX(A1:A4,MATCH(MAX(ABS(A1:A4)),ABS(A1:A4),FALSE))

In article <OGKhnF1KEHA.2100@TK2MSFTNGP10.phx.gbl>,
 "Ruan" <ruan@aegismed.com> wrote:

> Hello,
> 
> I want to find the maximum value in a column, but I want to ignore the
> negative and positive part of the value. Basically, the maximum difference
> from zero.
> 
> Example: the maximum value I am looking for will be -0.467.
> -0.467
> 0.345
> -0.253
> 0.411
> 
> Thanks
> Ruan
0
jemcgimpsey (6723)
4/26/2004 6:35:38 AM
Thanks that worked great.

Does excel allow for arrays in Conditional Formatting?

Ruan


"JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-6C561C.00353826042004@msnews.microsoft.com...
> One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):
>
>     =INDEX(A1:A4,MATCH(MAX(ABS(A1:A4)),ABS(A1:A4),FALSE))
>
> In article <OGKhnF1KEHA.2100@TK2MSFTNGP10.phx.gbl>,
>  "Ruan" <ruan@aegismed.com> wrote:
>
> > Hello,
> >
> > I want to find the maximum value in a column, but I want to ignore the
> > negative and positive part of the value. Basically, the maximum
difference
> > from zero.
> >
> > Example: the maximum value I am looking for will be -0.467.
> > -0.467
> > 0.345
> > -0.253
> > 0.411
> >
> > Thanks
> > Ruan


0
ruan (28)
4/26/2004 9:34:32 PM
Unfortunately, no.

In article <eDd3l09KEHA.1612@TK2MSFTNGP12.phx.gbl>,
 "Ruan" <ruan@aegismed.com> wrote:

> Does excel allow for arrays in Conditional Formatting?
0
jemcgimpsey (6723)
4/27/2004 7:30:11 AM
Reply:

Similar Artilces:

How can I minimalize the difference between extreme values on a c.
I'm making a chart with two values for class. One of the values is very small (3) and the other is very large (317). How can I make the axis values "break" so to speak, so that the difference between them is minimalized. For example, I need a portion that goes from 1-7 and another that goes from 300-700. I can't put them on different charts. I describe one technique on this page, and include links to others: http://peltiertech.com/Excel/Charts/BrokenYAxis.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutio...

how do I record times from track meets and find averages
I need toot been able keep track of athletes time and find averages. I have not been able to figure it out. go over to debra's site: http://www.contextures.com/excelfiles.html some files have been provided for download. check what fits you requirement -- Message posted from http://www.ExcelForum.com ...

excel 97 vba
cell C19 = product code drop down list cells N1:O449 = description of prod associated w/ dropdown list cell E19 = product description column cell I19 = price column cell P1:Q449 = list associated with product code drop down list Right now there is this code in cell E19: IF(C19<>""(C19,$N$1:$O$449,2,0),"") This code allows me to pick a code # from the drop down list in C1 (which is referenced to column N and O) and once selected, th corresponding product will appear in the product description cel (E19). I now am trying to set up a code in cell I19 so that once th...

Pie Chart
Good day, I have data organised as follows. Col A Col B (Header) ( % ) red 39% blue 0% white 24 % Green 10% Brown 0% My problem is that when i create a pie chart, it picks up the 0 valuses as well, and so the chart looks clustured, as i need to have the lables. Please advise how to tell the chart to omit 0 values. One option which i cannot use due to layout of the report is Hiding rows and then plotting only visible cells. Thanks Best regards Good Day, Jon, Thanks for the answer, but what i need is for the chart not to plot 0 values. SO that there are not too many slices of the pie....

Total a column from sheet 2 based on value in sheet 1
Col B Col C Col F Mary Team 1 $331.00 George Team 1 $222.00 Sam Team 2 $186.00 Tom Team 2 $100.00 Above is an example of my data on Sheet 2. On Sheet 1, I want to total all the total funds raised per Team shown on Sheet 2. I am trying to create a summary of what each team raised. I tried using the formula: =SUM(('sheet 2'!F2:F482=Sheet1!B2)) and I get just a dash in my total col. Can anyone provide some help? Thanks Look in HELP for the SUMIF() function -- Kind regards, Niek Otten Microsoft MVP - Excel "Nee...

Find File Fast
I am not sure how to search for this and therefore haven't had any luc finding a solution. I upgraded one of my users to Office 2000 from 97, previously in exce 97 she would go to open and search for a document by typing the firs few characters of the file name, the search would bring her to th general area she needed to be in and from there she would select he file. In Excel 2000 this no longer works as a search function when she type a letter -- nothing. Any help would be greatly appreciated! Thanks, Jil -- Message posted from http://www.ExcelForum.com If she's looking for ev...

Add Value to the Chart
I have a chart done from the table shown below. However, I want to put the value (date) in addition of the values (X) and (Y) axis. So far, I have been doing it manually. I wonder if there is any way to it automatically. Thanks in advance. Maperalia. Date "X" "Y" 6/21/2005 0 0 7/6/2005 147 0.008 7/18/2005 197 0.012 8/5/2005 255 0.016 8/18/2005 289 0.016 9/8/2005 337 0.006 9/19/2005 360 0.017 How would you like the date to appear? As a data label on each point? then use one of these handy data labeling add-ins: Rob Bovey's Chart ...

constant values
I have a very simple question, which I am not able to resolve though. I have created a query, which combines records from two tables. Works nicely. Now I would need to add several columns with either a) fix values, which will never change b) values which apply for all records, but which I would like to enter within a form every time I would like to run the query. The background is that I will use access as a mapping tool, so an original file will be uploaded and result in a table with the same name always. The query now takes the mappings for another table and combines the ...

eConnect -- where to find additional documentation and samples?
Hello, I'm working on my first eConnect implementation, and I'm finding the included documentation and samples a bit short of what I'd hope to see. My needs are fairly light (or at least I think so), but I've been having difficulty "digging deeper". Can anyone suggest a good resource (or groups of resources) that I can tap for support during this project? In other words, if this was your first eConnect project, where would you go for additional support outside of what was included in the eConnect SDK? The main goal of my implementation project is to post ...

Vlookup not seeing all values
Problem linking values from one wookbook / sheet to anouther. Almost all cells work fine but some do not, they do however if I copy/paste the lookup value into a "search all sheets" and double click the cell in the source sheet that contains the lookup value. Example: Look up cell for destination file on sheet1 = 1234 Look up cell from source file on sheet2 = 1234 Both are formated as "text" but the vlookup data is not transfered until I double click on the cell containing 1234 in the source file sheet2 then it works fine until I update the file aga...

Hover values in chart
I have a line chart with a secondary vertical axis with values displayed on the right of the chart. When I hover over this secondary data line I get a message box that gives me the "series", "point" and "value". The "point" however is a number depending on how far you are from the left side of the chart. If I hover over the primary data line the "point" displays the value on the x-axis. There doesn't seem to be a way to specify the data sequence for the secondary x-axis. How do I get the secondary data line to show the "point" va...

"implant" ActiveX ComboBox value into if eq'n
Morning all. Ok, I've got my combox active to where I can read the names in the list. I'd now like to have an if equation that will pull the value I select in the combobox. Do I have to write a secondary macro/function for that, or can I do it directly on the worksheet? Private Sub ComboBox1_Change() Select Case ComboBox1.Text Case "Item1" MsgBox ("Item1") Case "Item2" MsgBox ("Item2") Case "Item3" MsgBox ("Item3") Case "Item4" MsgBox ("It...

Sum values over range of dates
Need Help!!!! PLEASE! Here is the example... If I have a huge table of dates and values for those dates and need to sum based on a given date range, how do I sum it up? date 1 date 2 date 3 date 4 date 5 Sate 1 12 7 8 1 1 State 2 10 4 6 2 8 State 3 5 4 2 3 7 start date end date Sum would be??? Sate 1 2 5 17 State 2 1 3 20 State 3 2 4 9 But what is the formula I can use for this??? How can I set it up so I just need to change start and end dates and it will calculate automatically???? Please help! Thank You!!!! Assume: This data is in the range A1:F4. > date 1 date...

Mapping estimated value to totalamount
Hi, I define a map between opportunity estimated revenue and quote totalamount. I set the estimated revenue to be user provided and write a value by hand. The value is not carried to the totalamount field of the quote when I add a related quote. Instead it is calculated from the amount fields of the quote products. Is the totalamount field always system calculated, cannot we override it with the value in the estimated revenue? Thanks, Bilge Gul "b_gul_t" <bgult@discussions.microsoft.com> wrote in message news:840D5F58-34E8-4639-9A8C-C0508D9C9686@microsoft.com... > ...

Copying number to clipboard, subtracting 398 then pasting the value to overwrite the original
Hi. I am very new to this. I'd be really grateful if someone could help/guide me. I want to create a macro in Microsoft Word but I don't know visual basic. I want to be able to highlight a number then: - copy it to the clipboard - subtract 298 - paste the value to the Word document, overwriting the original text Hi John, There is no need to involve the clipboard if you are only changing the selected number. The following macro subtracts 298 from the selected number. Sub Subtract298() If IsNumeric(Selection.Text) Then Selection.Text = Val(Selecti...

Adding values
I have a spreadsheet with one column of names the names are repeated down column :A Colum :D rob 5 rob 2 martin 5 rob 6 martin 5 etc: in another column D I have values of numbers Is it possible to add up all the values in column D associated with Rob i.e. Rob =13 Martin =10 Thanks Rob Look in the help index for SUMIF -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "CrashMatRob" <crashmatrob@ntlworld.com> wrote in message news:uRgOzPhiJHA.3708@TK2MSFTNGP04.phx.gb...

Null Value
When I try to enter a date on a form I keep getting the following error message; "You tried to assign the Null value to a variable that is not a Variant data type (Error 3162). The help for this error recommends declaring the variable. The only problem is I do not have any modules created for the form. What can I do to stop this error message from happening? The form has a code module. Perhaps you mean you have not created any code in the form's code module? If you used a wizard to create, say, a command button there will probably be code in the form's module, so ...

Line Graph Excluding 0 Values
Dear, I have plotted a line graph consisting the data for the entire month. The data range is getting calculated automatically by a formula. 1 Dec - 100 2 Dec - 105 3 Dec - 0 4 Dec - 0 So on... The data for the days starting from 3 Dec is zero and the line graph suddenly goes down to zero... Is there any way that the line graph picks the data excluding the zero values? I mean the line graph should only take the data for 1 Dec and 2 Dec without changing the data range. Thank you. one way might be to hide columns for 3 Dec, 4 Dec etc. On 2 Gru, 08:19, Sasikiran ...

Calculating Between Values
Hope you can help me! I have a column on one spreadsheet called "Platts Ports" Now, I want to analize column E. I want to be able to pick out all the values between 0 & 0 (>0 &<2). I then want to say the following: Where value = between 0-2 (COUNT) then add up column F where all these fields apply. Then divide COUNT(E) by the total of F( the sum we just calculated) To top this off, I need to do this from another sheet (within the same Spreadsheet) Hope you understand. Email me at RBotley@Gmail.com if you need me to priovide more info! Thanks in advance -- ...

Rounding up Time Values
Hi I am using the formula below to round time values up,in this case t the next five minute increment =(TIME(HOUR(BB14),CEILING(MINUTE(BB14),BB13),0)) BB13 = 5 BB14 = 16:00:02 This formula only rounds up to 16:05:02 if the time is 16:01:00 o greater,i would prefer it if the formula could make the time round u as soon as one second has passed, 16:00:01 and not when one minute ha passed. Can this be done Ad -- Message posted from http://www.ExcelForum.com What is in BB14? You can just use =CEILING(BB14,5/1440) will do what you want Or if there are dates as well you can just format...

IF formula to round up values depending upon their outcome
I am using an IF formula to calculate between two cells, one is J (width) the other is K (length). Currently these formulas give an answer that then has to be rounded up based on the decimal place. I need the formula to also round up the amount to quarter increments. For example if the answer is 1.17 then the formula needs to make it 1.25, if it is 1.33 then the formula needs to make it 1.5, and finally if it is 1.63 then the formula needs to make it 1.75. So how do I add or make the formula round up to quarter increments? The formula that I am using is: =IF(J41<=3,K41/4,IF(J4...

Merging Workbook Table data Based upon Value comparisons
I have two workbook tables (Two different workbooks) with two matching column names. What I wish to do is to merge values from one table to another, but ONLY for those records inwhich these two columns have matching values. Would this be possible? Jay Are you saying you have two workbooks, or are the tables within one workbook? "jayceejay" <jayceejay@discussions.microsoft.com> wrote in message news:AC73B2C7-83EF-4D27-A464-32AEEE7D4214@microsoft.com... >I have two workbook tables (Two different workbooks) with two matching >column > names. What I wish to do ...

Finding the folder?
I used to use windowns 98se and was able to find the folder which contained the things for the outbox, sent items and such. I used to just delete those and it would erase all the stuff in the folders. I'm using windows xp and can't seem to figure out how to do it. Anyone know what folder they are in? Make sure you include hidden and system files and folders when searching for pst-file. By default it is located in C:\Documents and Settings\%username%\Local Settings\Application Data\Microsoft\Outlook\ You can also locate the file by using Rightclick the root folder (probably Ou...

How find appointments that *start* 12a.m.?
I'm using Outlook 2003. Is there any way to find all appointments that *begin* at 12 a.m.? Hint, it is *not* this: FIND (button) > Options (pull down) > Advanced > (Appointments and Meetings) > Advanced (tab) > Field (pull down) > Date/time fields > start (...which only give options like yesterday, today, last week, etc. ... but not a specific *time* like 12 a.m.) The reason I need to see these is, I may have some holdover things from several upgrades and and old Palm that may have been "no time" in the Palm, but somehow got tagged as 12 a.m. at so...

Reference a cell value in a formula
I have the following formula: =SUMPRODUCT((MONTH(Summary!$F$7:$F$15000)=1)*1) I want the "15000" to be replaced by the varaible value of a cell in an other worksheet ("Summary"). I tried using INDIRECT but it didn't work. Thanks in advance =SUMPRODUCT((MONTH(OFFSET(Summary!$E$7,0,0,C4,1))=1)*1) C4 in this case is the cell that holds the number of items to be used in the calculation -- If the post is helpful, please consider donating something to an animal charity on my behalf ..... and click Yes "Leon" wrote: > I have the f...