none
Developing a formula in Excel based on a range RRS feed

  • General discussion

  • Hi All.

    I am trying to write a formula as follows.

    If B1 has a value of between 100 and 200 then C1 = B1 * 0.5

    If B1 has a value of between 200 and 400 then C1 = B1 * 0.4

    If B1 has a value of between 400 and 600 then C1 = B1 * 0.3

    If B1 has a value of between 600 and 800 then C1 = B1 * 0.25

    If B1 has a value of between 800 and 1000 then C1 = B1 * 0.2

    How can i write this within a formula in excel.

    Monday, September 23, 2019 11:42 AM

All replies

  • For example:

    =B1*LOOKUP(B1,{100,200,400,600,800},{0.5,0.4,0.3,0.25,0.2})

    Alternatively, place the thresholds and multiplication factors in a range of cells, for example F1:G5, and use

    =B1*VLOOKUP(B1,$F$1:$G$5,2)

    This makes it easier to adjust the parameters.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, September 23, 2019 4:23 PM