Forum Discussion

Luke Pennington's avatar
Luke Pennington
Copper Contributor
May 08, 2018

Problem with if function

Hi,

 

hope you can help and I'm sure it is a simple issue. I'm using "IF" function for the first time and I have attached a screenshot of the data that I'm using. This is just practice data that I made up, so I'm trying to work out the bonus for each of my employees.

 

When it comes to the bonus I used the formula: =IF(G2>800, E2*1.6+E2*F2,E2*F2) which gave me the 475.2 result. So far, so good! The issue I keep getting is when I drag down on the bonus cell to copy the formula for all the other employees my values for everything changes. Pay rate changes, hours worked changes, hire date changes etc. So it completely messes up the formula I'm trying to use. I've attached a second screenshot AFTER I have dragged and copied the formula into the other boxes.

 

Now the hire date, tenure, pay rate, hours worked and sales were all made up using the RANDBETWEEN formula. I'm sure the RANDBETWEEN is causing the issue, but I just don't know why it is. Is anyone able to shed any light?

 

Kindest regards,

 

Luke

excel
Excel Online
office 365

2 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 08, 2018

    Hi Luke,

     

    RANDBETWEEN generates new sets of numbers every time you edit any cell in the sheet. To avoid that you may generate your numbers, after that copy and paste them as values on the same place.

     

    By the way, your formula could be

    =E2*(1.6*(G2>800)+F2)
    • Luke Pennington's avatar
      Luke Pennington
      Copper Contributor
      May 08, 2018
      Hi,

      Thank you so much for your quick, responsive reply. Wasn’t aware that RANDBETWEEN would repeatedly generate random numbers.

      Will try that and thank you for the alternative formula! Still getting used to excel.

      Kind regards,

      Luke