• 466K Members
• 8,684 Online
• 563K Conversations

## Formulas

Occasional Contributor

# Formulas

Hi
I am new to excel as I have recently started a new job so this may be a simple one to answer. I need to create a table which will have 3 columns, START OF WORK AREA, END OF WORK AREA, AND OVERHEAD CABLES. I want the table to highlight dangerous work areas inbetween two locations. So say if I input 4.0 under START OF WORK AREA,6.0 under END OF WORK AREA the OVERHEAD CABLES column will list all areas with the cables in. I thought of conditional format but I can't highlight certain numbers that are between the range.
15 Replies

# Re: Formulas

I was hoping it would list the locations of the overhead cables highlighted in a colour e.g work area 4.0 to end 8.0, overhead cable 7.6 this in red?.

# Re: Formulas

Given the start and end of the work areas, how do you determine the work areas between them and which work area is dangerous?

# Re: Formulas

The work are is the whole area between start and end. And I wanted the condition set so if any of the dangerous locations are inbetween the range they will come up in separate column highlighted in red. The work location will be changed daily so all the dangerous locations I will need in the formula or conditional format so as the work area changes only the dangerous locations in the new range are highlighted.

# Re: Formulas

It seems you already have a given list of dangerous locations and you want to highlight such dangerous locations that are between the start and end work areas. Is that what you want to achieve?

# Re: Formulas

Yes that's what I require. Just not sure how. Thanks

# Re: Formulas

Now it’s clear that dangerous work areas are pre-determined. How about work areas between start and end? How are they determined? At what increments are intermediate work areas determined? At increments of 0.1, such that 9 work areas are between 4.0 and 5.0? I hope so.

# Re: Formulas

The work area could be the whole area between say 0.4 and 4.2 and in this area there may be only one or as many as 4 or 5 areas of danger. So in the whole work area there could be overhead cables at 0.6 and 4.1 so these 2 figures would be displayed in red in column 3.

# Re: Formulas

Do you also have a pre-determined list of work areas between the start and end?

# Re: Formulas

The work area can range from 0.0 to 65.0. Any where inbetween

# Re: Formulas

Let me succinctly state your aim this way:
You want to highlight in red fill color the dangerous work areas that are between the start and end work areas. Thus, you need 2 lists:
1. List of dangerous work areas, which you admitted is pre-determined and so you have no problem with that; and
2. List of work areas between the start and end, wherein my last question was anchored.
If list 2 is also pre-determined, you just have to determine the instances of list 1 in list 2 and highlight those instances in red fill color.
Back to my question, such that list 2 is not pre-determined. In such case, you have to determine it and you have to know how? Perhaps at increments of 0.1 from start.
Without list 2, there is nothing to which you can compare list 1. Consequently, there would be nothing to highlight.

# Re: Formulas

Yes they would be in increments of 0.1 from the start.

# Re: Formulas

In the attached file, the danger areas that are within the latest work area are highlighted with red fill color.

# Re: Formulas

That's brilliant. Looking at that formula I would never of stood a chance. Thanks for your help it's much appreciated. I will get to work on it now.

# Re: Formulas

Welcome and good luck on your new job.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies