excel help - different limits.

dr_volki · 16 Mar 2017 at 12:43

Hi,

it's probably quite easy to do, but i can't figure it out. :confused:

i have a spreadsheet for some electrical testing results we have and we have different limits for line impedance depending on the type of breaker.

ideally i'd like to get cells highlighted that are over the maximum limit depending on the breaker.
do i need to do some kind of look up thing?

thanks

Sui · 16 Mar 2017 at 12:48

Sounds like what you are after is conditional formatting.

It's an option in the Home ribbon.

dr_volki · 16 Mar 2017 at 13:18

Sui said:
Sounds like what you are after is conditional formatting.

It's an option in the Home ribbon.

thought of it, but don't know if i can do the following

i.e.
breaker C45N 10A type 2 - limit is 2.63
breaker RCBO C10 - limit is 1.84
there are a few more options.

what i would've like to do is check cell x for type of breaker. from there you know the max limit.
then highlight results cell if it's over.

joeyjojo · 16 Mar 2017 at 13:45

Use an IF to return something if it's over and something else if it's under (e.g. 1 and 0), then highlight as Sui says.

AHarvey · 16 Mar 2017 at 13:55

post a screenshot of the spreadsheet and it'll be easier to see what you need and the best way to format it

dr_volki · 17 Mar 2017 at 08:59

main spreadsheet:

limits:

basically what i'd like for ease of reading and without having to flip within different sheets.

to highlight column N (line Z)
if it's over the limit based on the breaker that's used in column O

Grrrrr · 20 Mar 2017 at 11:07

In the limits table column A isn't unique, e.g. C45N appears twice with two different ratings and therefore two different safety margins. That table needs a unique identifier. I would suggest adding a new column in before Column A called "Unique Identifier" (or whatever you like) and the formula would be something like =$B5&"|"&$C5&"|"&$D5 which would display C45N|10|2.

After doing that, in the main spreadsheet highlight Column N and add new Conditional Formatting with the formula:

=$N1>VLOOKUP($O1&"|"&$P1&"|"&$Q1,limits!$A:$F,6,FALSE)

The yellow text is the sheet name of the limits table, you'll probably need to edit that. If the data in column N is bigger than the corresponding limit in the limit table then the formula returns TRUE which triggers the conditional formatting (which i assume you'll set to highlight red or something).

dr_volki · 20 Mar 2017 at 13:14

Grrrrr said:
In the limits table column A isn't unique, e.g. C45N appears twice with two different ratings and therefore two different safety margins. That table needs a unique identifier. I would suggest adding a new column in before Column A called "Unique Identifier" (or whatever you like) and the formula would be something like =$B5&"|"&$C5&"|"&$D5 which would display C45N|10|2.

After doing that, in the main spreadsheet highlight Column N and add new Conditional Formatting with the formula:

=$N1>VLOOKUP($O1&"|"&$P1&"|"&$Q1,limits!$A:$F,6,FALSE)

The yellow text is the sheet name of the limits table, you'll probably need to edit that. If the data in column N is bigger than the corresponding limit in the limit table then the formula returns TRUE which triggers the conditional formatting (which i assume you'll set to highlight red or something).

will try that,
thanks a bunch

dr_volki · 21 Mar 2017 at 08:50

@Grrrrr worked a treat.
thanks a lot