EXCEL FUNCTIONS FOR COMPARING VALUES

Question

I have a hidden macro with some values in several columns. In my main and only tab visible to the user, I want to compare if the value of the Macro’s J2 cell is greater than 4 , if it is greater, in my main tab in the J10 cell show " APPROVED", if less than 4 and greater than 0 , show "DISAPPROVED", if equal to 0 ,"HAS NO VALUE . I’m trying to use the =SE function but I’m not getting it.

Answer 1

Assuming that the spreadsheet in which the cell J2 is called PLAN1. The formula in 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")))

Answer 2

There is how to reduce formula and also how to treat possible errors.

REDUCED FORMULA:

Since with two conditions not met, 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 it’s not at the same time less than 4 and greater than zero, all remaining numbers must be less than or equal to zero.

ADDRESS ANY ERRORS:

Values above ten or negative indicate data entry failure, so do so:

=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!")))

Note that it is first checked if the value is greater than ten, and then, not being met all the following conditions, only the negative values, then, just indicate the error at the end!