Cursuri Excel (Conditional Formatting) “20% above or below the expected amount”.

Intrebare:

“Hello,

I have a table with 4 columns and the last two are represented by Expected Weight Actual Weight. I have to use conditional formatting to highlight (in red) THE ORDER NUMBER for orders which had an actual weight more than 20% above or below the expected amount.

can you help me with this?”

Pentru rezolvare o sa utilizez documentul excel: https://cursurimicrosoft.com/fisierelucru/rezolvareconditonalformatting.xlsx.

Selecteaz coloana “The Order Number” fara cap de coloana (de la celula B3),  in tab-ul Home  aleg subcomanda “New Rule” de la butonul “Conditional Formatting”, grupul Styles.

image

image

In fereastra “New Formatting Rule” aleg “Use a formula to Determine wich cells to format”.

SNAGHTML7f6431

In caseta “Format values where this formula is true:” din zona “Edit the Rule Description:”, scriu urmatoarea formula.

=OR($D3/$C3>1.2,$D3/$C3<0.8)

image.

Pentru setarea culorii cind este indeplinita una din cele doua conditii de la functia “OR”, apas pe butonul Fomat.

image

In fereastra “ Format Cells” din optiunile corespunzatoare tab-ului “Fill” aleg culoare Rosu pentru colorarea celulei din  coloana “The Order Number” unde este o variatie de peste 20% sau sub 20% de greutate.

SNAGHTML9068e2

SNAGHTML93b166

image

Tagged , , , , , , , , , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.