[Solved] 0-100 Scale, +30 -30

[BTH456]

Hi Guys

I need help, I cant work out how to do this, see attached.


Its Floorcovering Colour Light Reflectance Values, to Insert Colour Light Reflectance Values, in order to comply with Disability Discrimination Act Regulations they have to be at least 30 points different.

Its quiet simple really. Basicly +30 and -30 with a floor of 0 and a ceiling of 100, but I cant work it out can anyone help?

Example.
FloorCovering LRV //// Insert Colour LRV Range.
56.76 ////////////////// 26.76 - 86.76
78.94 ///////////////// 0 - 48.94
28.43 ////////////////// 58.43 - 100

I made a 4 tables on the sheet, with all the results possible but i dont know the next step, if it is even the correct or best method. if I know how to reference Cells it might work.

I might just need some fresh eyes on this, forgive me if its bleeding obvious.

I look forward to any response.

Thanks in advance.

Benjamin.

[John_Ha]

Welcome to the forum.

Code: Select all

IF((B18+30)<100;(B18+30);100)
IF((B18-30)>0;(B18-30);0)

The first says:

If (B18+30) is less than 100, then the cell value should be (B18+30). If not, which means (B18+30) is more than 100, the cell value should be 100.

As a new poster you will find much useful information in the Calc Guide and the Calc Tutorials. May I suggest you bookmark the pages.

Press F1 to access the Help screen and search for your problem. When a pop-up window opens, click the Help button for extensive help on that function - it is often more comprehensive than the manual.

[RusselB]

One option is to take your 4 tables and make them as one table that has the LRV sorted in order, then you could use a couple of formulas that use the VLOOKUP function.
The other option I thought of, is to use formulas that use the MIN or MAX functions, but some of the details you are showing are making it difficult for me to figure out just how those formulas would be referenced.
At first I thought the low could be done as

Code: Select all

=max(0;B6-30)

using B6 as the entry for LRV as you did in your spreadsheet,, and the high could be

Code: Select all

=min(100;B6+30)

While these work for your example of 56.76, they don't work with your other two examples... though now that I look at them, I am seeing some ideas that I'd like to test

[BTH456]

Welcome to the forum.

Code: Select all

IF((B18+30)<100;(B18+30);100)
IF((B18-30)>0;(B18-30);0)

The first says:

If (B18+30) is less than 100, then the cell value should be (B18+30). If not, which means (B18+30) is more than 100, the cell value should be 100.

As a new poster you will find much useful information in the Calc Guide and the Calc Tutorials. May I suggest you bookmark the pages.

Press F1 to access the Help screen and search for your problem. When a pop-up window opens, click the Help button for extensive help on that function - it is often more comprehensive than the manual.

That's fantastic, thank you very much.

I am also interested in how i might get the same results using a "reference system" and the charts i have made,
is that possible at all?

With the VLOOKUP function as RusselB Suggests?

I will of course use the Calc Tutorials, as you suggested.

Thanks again.

[BTH456]

Hi Guys,

I thought you might like to see what I done with the info you helped me with. see attached.

I am very happy with it.

I used the conditional formatting to change colours of numbers and backgrounds, love it learned lots.

Thanks again.