Forum Discussion

ChristavanDee's avatar
ChristavanDee
Copper Contributor
Apr 28, 2021
Solved

Formula

Hallo, how do I write in a Formula:

I have a column M and is has 10 numbers. In each cell ( M1, M2, M3, M4 etc) 1 number

This number can have the value 1 or smaller than 1.

If the number is 1.00 , then nothing happens, if it is smaller than 1.00, then the following must happen:

Suppose:

M1 = 1.00  N1 = 6,70
M2 = 1.00  N2 = 10,50
M3 = 1.00  N3 = 21,14
M4 = 0.59  N4 = 31.69


Then (N4-N3)*n4 + N3

 

But when M4 =1 and M5 also,  then M6 could be < 1. The value of M may change. This is related to the height of a % what you enter in another cell.

 

Groet, Christa van Dee, The Netherlands

 

Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    May 03, 2021

    ChristavanDee 

    Normaal gesporken is het niet de bedoeling om bijgevoegde bestanden weer te verwijderen, omdat dat het voor anderen onbegrijpelijk maakt. Maar je kan het als volgt doen:

    Klik op de v in de rechter bovenhoek.

    Kies 'Edit Reply' uit het menu.

    Beweeg de muisaanwijzer over het bijgevoegde bestand onderin het antwoord.

    Klik op het kruisje aan de rechterkant.

    Klik op Post.

18 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Apr 28, 2021

    ChristavanDee 

     

    =IF(M1<1,"",($N$4-$N$3)*$N$4+$N$3)

     

    I would be happy to know if I could help.

     

    Nikolino

    I know I don't know anything (Socrates)

    * Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

    • ChristavanDee's avatar
      ChristavanDee
      Copper Contributor
      Apr 28, 2021
      N. Years. M.
      2,67 1 1,00
      6,70 1,3 2 1,00. (6,70 = (2*2,67 + 1,3)
      10,50 2,11 3 1,00. (10,50 = 3*2,67 + 2*1,3 + 2,11)
      21,14 2,17 4 1,00. etc
      31,69 2,24 5 0,43
      45,30 3,07 6 0,00
      61,31 2,39. 7 0,00
      76,92 3,29 8 0,00
      103,32 3,42 9 0,00
      128,70 2,67 10 0,00

      Then (N4-N3)*n4 + N3. will be 25,66

      M = IRR but I want to know, how many job years it takes to earn the investment back
      N = FTE

      So when my turnover changes, M will change but also my number of FTE. And I want a formula to calculate Job years (BEP)
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Apr 28, 2021

    ChristavanDee 

    I'm afraid that is not very clear. I assume that you want the result of a calculation to appear in the first row that has a value <1 in column M, but where? And what if the next row has a value of 1 again? Or a value <1? Please try to be as specific as possible.