Posted October 17, 2013 Basically I have a large set of data from a Physics experiment. However due to the equipment used I have a lot of anomalies. I have been trying to get Excel to highlight the value in each column that is the highest value that isn't an anomaly. Example of the parameters: 1. Highest value with column 2. More than 0 3. Less than 2.5 Can anybody help with a formula for this? CPU: AMD FX-8350 | CPU Cooler: H80i | Motherboard: Asus M5A99FX PRO R2.0 | RAM: 8GB Kingston Beast 1866MHz Case: Define R4 | GPU: Gigabyte GTX 780ti | PSU: Corsair CX600M | SSD: 250GB Samsung 840 EVO .... and a Partridge in a pear tree! Link to comment Share on other sites More sharing options... Link to post Share on other sites More sharing options...
Posted October 17, 2013 highlight as in different formatting or put value in cell? Mini-Desktop: NCASE M1 Build LogMini-Server: M350 Build Log Link to comment Share on other sites More sharing options... Link to post Share on other sites More sharing options...
Posted October 17, 2013 Author highlight as in different formatting or put value in cell? Different formatting (eg. Highlight Green) CPU: AMD FX-8350 | CPU Cooler: H80i | Motherboard: Asus M5A99FX PRO R2.0 | RAM: 8GB Kingston Beast 1866MHz Case: Define R4 | GPU: Gigabyte GTX 780ti | PSU: Corsair CX600M | SSD: 250GB Samsung 840 EVO .... and a Partridge in a pear tree! Link to comment Share on other sites More sharing options... Link to post Share on other sites More sharing options...
Posted October 17, 2013 What do you mean by anomaly? Can you given an example of a column? Also do you want it to be PER column? or something, don't really understand what you want... You can use conditional formatting to do the between 0 and 2.5 thing, but I dunno what you mean by anomaly... PC SYSTEM: Fractal Design Arc Midi R2 / i5 2500k @ 4.2ghz / CM Hyper 212 EVO / Gigabyte 670 OC SLI / MSI P67A-GD53 B3 / Kingston HyperX Blue 8Gb / WD 2tb Storage Drive / BenQ GW2750HM - ASUS VE248H - Panasonic TX-P42ST60B/ Corsair AX750 / Logitech K360 / Razer Naga / Plantronics Gamecom 380 / Asus Xonar DGX / Samsung 830 256gb / MEDIA : eMachine ER1401 running OpenELEC XBMC with Seagate STBV3000200 3TB Hard Drive - Panasonic TX-P42ST60B Link to comment Share on other sites More sharing options... Link to post Share on other sites More sharing options...
Posted October 17, 2013 Google is your friend... http://office.microsoft.com/en-gb/excel-help/customize-a-parameter-query-HP010216113.aspx Ex Intel Response Squad Member /FAQ And Guide - CPU Choice/ | /How To Effectively Use The Search Function/ /How A CPU Works/ | /Windows 7 Customization/ | /CODE Keyboard Review/ Link to comment Share on other sites More sharing options... Link to post Share on other sites More sharing options...
Posted October 17, 2013 Author What do you mean by anomaly? Can you given an example of a column? You can use conditional formatting to do the between 0 and 2.5 thing, but I dunno what you mean by anomaly... An anomaly is a value that doesn't fit the general trend. For example the majority of the values may range from 0 to 3, therfore 15 would be an anomaly. Here is one of the columns: https://docs.google.com/file/d/0B1QNhsYJudg7Y05xdGxrYW1TZFk/edit?usp=sharing CPU: AMD FX-8350 | CPU Cooler: H80i | Motherboard: Asus M5A99FX PRO R2.0 | RAM: 8GB Kingston Beast 1866MHz Case: Define R4 | GPU: Gigabyte GTX 780ti | PSU: Corsair CX600M | SSD: 250GB Samsung 840 EVO .... and a Partridge in a pear tree! Link to comment Share on other sites More sharing options... Link to post Share on other sites More sharing options...
Posted October 17, 2013 Author Google is your friend... http://office.microsoft.com/en-gb/excel-help/customize-a-parameter-query-HP010216113.aspx I could be wrong, but I don't think that does what I want it to do. CPU: AMD FX-8350 | CPU Cooler: H80i | Motherboard: Asus M5A99FX PRO R2.0 | RAM: 8GB Kingston Beast 1866MHz Case: Define R4 | GPU: Gigabyte GTX 780ti | PSU: Corsair CX600M | SSD: 250GB Samsung 840 EVO .... and a Partridge in a pear tree! Link to comment Share on other sites More sharing options... Link to post Share on other sites More sharing options...
Posted October 17, 2013 An anomaly is a value that doesn't fit the general trend. For example the majority of the values may range from 0 to 3, therfore 15 would be an anomaly. Here is one of the columns: https://docs.google.com/file/d/0B1QNhsYJudg7Y05xdGxrYW1TZFk/edit?usp=sharing Erm....I still don't understand, I thought this was something complicated o_o From what I understand from that....you want to be able to highlight everything in a column that is between say 0 and 3 while leaving alone values that don't fit those parameters (aka not between 0 and 3)? That is very simple, I'm sure I'm missing something though... PC SYSTEM: Fractal Design Arc Midi R2 / i5 2500k @ 4.2ghz / CM Hyper 212 EVO / Gigabyte 670 OC SLI / MSI P67A-GD53 B3 / Kingston HyperX Blue 8Gb / WD 2tb Storage Drive / BenQ GW2750HM - ASUS VE248H - Panasonic TX-P42ST60B/ Corsair AX750 / Logitech K360 / Razer Naga / Plantronics Gamecom 380 / Asus Xonar DGX / Samsung 830 256gb / MEDIA : eMachine ER1401 running OpenELEC XBMC with Seagate STBV3000200 3TB Hard Drive - Panasonic TX-P42ST60B Link to comment Share on other sites More sharing options... Link to post Share on other sites More sharing options...
Posted October 17, 2013 Author Erm....I still don't understand So you want to be able to highlight everything in a column that is between say 0 and 3 while leaving alone values that don't fit those parameters? I want to highlight the highest value, that is within 0 and 3 CPU: AMD FX-8350 | CPU Cooler: H80i | Motherboard: Asus M5A99FX PRO R2.0 | RAM: 8GB Kingston Beast 1866MHz Case: Define R4 | GPU: Gigabyte GTX 780ti | PSU: Corsair CX600M | SSD: 250GB Samsung 840 EVO .... and a Partridge in a pear tree! Link to comment Share on other sites More sharing options... Link to post Share on other sites More sharing options...
Posted October 17, 2013 Different formatting (eg. Highlight Green) easiest way is to put the value in an actual cell via an array formula e.g. =MAX(IF(AND(A1:A100>1;A1:A100<10);A1:A100)) =MAX(IF(A1:A100>0;IF(A1:A100<2.5;A1:A100))) Mini-Desktop: NCASE M1 Build LogMini-Server: M350 Build Log Link to comment Share on other sites More sharing options... Link to post Share on other sites More sharing options...
Posted October 17, 2013 Author easiest way is to put the value in an actual cell via an array formula e.g. =MAX(IF(AND(A1:A100>1;A1:A100<10);A1:A100)) That defiantly looks similar to what I want, however it gives me an error in excel. CPU: AMD FX-8350 | CPU Cooler: H80i | Motherboard: Asus M5A99FX PRO R2.0 | RAM: 8GB Kingston Beast 1866MHz Case: Define R4 | GPU: Gigabyte GTX 780ti | PSU: Corsair CX600M | SSD: 250GB Samsung 840 EVO .... and a Partridge in a pear tree! Link to comment Share on other sites More sharing options... Link to post Share on other sites More sharing options...
Posted October 17, 2013 easiest way is to put the value in an actual cell via an array formula e.g. =MAX(IF(AND(A1:A100>1;A1:A100<10);A1:A100)) YES! PROFILEYEAH What do people even put in these things? Link to comment Share on other sites More sharing options... Link to post Share on other sites More sharing options...
Posted October 17, 2013 Calling @wkronbaek Main Rig: -FX8150 -32gb Kingston HyperX BLUE -120gb Kingston HyperX SSD -1TB WD Black -ASUS R9 270 DCUII OC -Corsair 300r -Full specs on Profile Other Devices: -One Plus One 64gb Sandstone Black -Canon T5 -Moto G -Pebble Smartwatch -Nintendo 2DS -G27 Racing Wheel #PlugYourStuff - 720penis - 1080penis - #KilledMyWife - #LinusButtPlug - #HashtagsAreALifestyle - CAR BOUGHT: 2010 Corolla Link to comment Share on other sites More sharing options... Link to post Share on other sites More sharing options...
Posted October 17, 2013 Author easiest way is to put the value in an actual cell via an array formula e.g. =MAX(IF(AND(A1:A100>1;A1:A100<10);A1:A100)) I don't suppose you could briefly explain how to add an array formula as I cant seem to get it to work. CPU: AMD FX-8350 | CPU Cooler: H80i | Motherboard: Asus M5A99FX PRO R2.0 | RAM: 8GB Kingston Beast 1866MHz Case: Define R4 | GPU: Gigabyte GTX 780ti | PSU: Corsair CX600M | SSD: 250GB Samsung 840 EVO .... and a Partridge in a pear tree! Link to comment Share on other sites More sharing options... Link to post Share on other sites More sharing options...
Posted October 17, 2013 I don't suppose you could briefly explain how to add an array formula as I cant seem to get it to work. put it into the forumla field and press ENTER+SHIFT+CTRL Mini-Desktop: NCASE M1 Build LogMini-Server: M350 Build Log Link to comment Share on other sites More sharing options... Link to post Share on other sites More sharing options...
Posted October 17, 2013 ok I am sorry the first solution might not work, change it to instead: =MAX(IF(A1:A100>0;IF(A1:A100<2.5;A1:A100))) enter with ctrl+shift+enter example: https://dl.dropboxusercontent.com/u/3247020/map1.xlsx Mini-Desktop: NCASE M1 Build LogMini-Server: M350 Build Log Link to comment Share on other sites More sharing options... Link to post Share on other sites More sharing options...
Posted October 17, 2013 Author ok I am sorry the first solution might not work, change it to instead: =MAX(IF(A1:A100>0;IF(A1:A100<2.5;A1:A100))) enter with ctrl+shift+enter example: https://dl.dropboxusercontent.com/u/3247020/map1.xlsx Thanks for that, I'll give it a go when I am home in a bit.Sent from my iPhone using Tapatalk - now Free CPU: AMD FX-8350 | CPU Cooler: H80i | Motherboard: Asus M5A99FX PRO R2.0 | RAM: 8GB Kingston Beast 1866MHz Case: Define R4 | GPU: Gigabyte GTX 780ti | PSU: Corsair CX600M | SSD: 250GB Samsung 840 EVO .... and a Partridge in a pear tree! Link to comment Share on other sites More sharing options... Link to post Share on other sites More sharing options...
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now