 New User trying to figure out a formula

I am trying to write a formula that will cause the cell value to be multiplied against 3 variables depending on the value of the cell.

If cell J16 is less than 2501 then multiply times 0.55

If cell J16 is greater than 2500 but less than 7501 then multiply times 0.60

If cell J16 is greater than 7500 then multiply times 0.65

I am trying to use IFS but cant seem to get it and this is what I have so far:

=IFS(J16<2501,J16*0.55,J16>7500,J16*0.65,2500<J16<7501,J16*0.6)

3 Replies

Re: New User trying to figure out a formula

I am getting a #N/A error and Value not available error. Value of J16 is based on a formula of J5:J15

Re: New User trying to figure out a formula

with IF formula

=IF(J16<2501,J16*0.55,IF(J16<7501,J16*0.6,IF(J16>7500,J16*0.65)))

with IFS formula it should be

=IFS(J16<2501,J16*0.55,J16<7501,J16*0.6,J16>7500,J16*0.65)

As i don't work with Office365 or 2021 i can't test IFS formula but i know the syntax of IFS and it should work as well.

Re: New User trying to figure out a formula

@CSBOHYEA  .... Try:

=J16*IFS(J16<2501, 0.55, J16<7501, 0.60, TRUE, 0.65)

or

=J16*IF(J16<2501, 0.55, IF(J16<7501, 0.60, 0.65))

-----

@CSBOHYEA  wrote: ``I am getting a #N/A error and Value not available error.``

IFS returns #N/A when none of the conditions applies.  Your formula is, in part:

=IFS(...., 2500<J16<7501, J16*0.6)

That does not test the condition that you intended.

It is parsed as (2500<J16)<7501.  Since 2500<J16 returns TRUE or FALSE, effectively the last condition is TRUE<7501 or FALSE<7501.  But Excel treats logic values as greater than any number.  So, the last condition is never true.  Thus, none of the conditions applies.