I have a hidden macro with some values in several columns. In my main tab and visible to the user, I want to compare if the value of cell J2 of the Macro is greater than 4, if it is greater, in my main tab in cell J10 show "APPROVED", if smaller than 4 and greater than 0, show "REJECTED", if equal to 0, "DOES NOT HAVE VALUE. I'm trying to use the = SE function but I can not.
EXCEL FUNCTIONS TO COMPARE VALUES
-1
asked by anonymous 08.06.2016 / 13:35
2 answers
1
There is a way to reduce the formula and also how to handle any errors .
REDUCED FORMULA:
Since with two conditions not satisfied, the third is met, we have:
=SE(Plan1!J2 > 4; "APROVADO"; SE(E(Plan1!J2 <= 4; Plan1!J2 >0); "REPROVADO"; "NÃO POSSUI VALOR"))
If the value is not greater than 4 , and is not both less than 4 and greater than zero , < strong> the remaining numbers are all necessarily less than or equal to zero .
TREAT EVENTUAL ERRORS:
values or negative values indicate data entry failure, so do this:
=SE(Plan1!J2 > 10; "ERRO: VALOR MAIOR DO QUE DEZ!"; SE(Plan1!J2 > 4; "APROVADO"; SE(E(Plan1!J2 <= 4; Plan1!J2 >0); "REPROVADO"; SE(Plan1!J2 =0; "NÃO POSSUI VALOR";"ERRO: VALOR NEGATIVO!")))
Notice that is checked first if the value is greater than ten , and then not following all the following conditions , only negative values remain , so just indicate the error at the end!
09.06.2016 / 21:23
2
Assuming that the worksheet in which cell J2 is called PLAN1. The formula for column J10 would be as follows:
=SE(Plan1!J2 > 4; "APROVADO"; SE(E(Plan1!J2 <= 4;
Plan1!J2 >0); "REPROVADO"; SE( Plan1!J2 = 0; "NÃO POSSUI VALOR")))
08.06.2016 / 14:23