Forum Discussion

ashunjoy_1911's avatar
ashunjoy_1911
Copper Contributor
Sep 03, 2020
Solved

Excel- Split a number

Dear Sir, I am facing problem in excel for splitting a number into different random parts Like I want to split 45 into 7 random parts "1st part between 0-12"   " 2nd part between 0-12"   " 3rd ...
excel
Formulas and Functions
  • Ramiz_Assaf's avatar
    Ramiz_Assaf
    Sep 03, 2020

    ashunjoy_1911 

     

    Dear Dr. Sharma

    Please find a file that has a VBA script.

     

    input your number in cell A1 (for example 45)

     

    the function will use randbetween... until the sum will be 45

     

    if you want 55, just change the value in cell A1

     

    best regards

     

Ramiz_Assaf's avatar
Ramiz_Assaf
Iron Contributor
Sep 03, 2020

ashunjoy_1911 

 

Press F9 to generate solutions

accepted solutions will become green

keep pressing F9 until all the column becomes green

 

hope this solution work, I dunno what to do with fractions (round up or down)

  • Qusai_Janada's avatar
    Qusai_Janada
    Copper Contributor
    Dec 23, 2022

    Ramiz_Assaf 

     

    Hey Ramiz, can you help me out?

    I tried your formula, but it does not work here.

     

    https://docs.google.com/spreadsheets/d/1kYxrx9zcjY2Ap1o4nAw5sYs5d7BAhXVD/edit?usp=sharing&ouid=100527059548267574176&rtpof=true&sd=true 

  • ashunjoy_1911's avatar
    ashunjoy_1911
    Copper Contributor
    Sep 04, 2020

    Ramiz_Assaf 

     

    Thank you Assaf

     

    Actually the excel shows me error in the formula as I am using Office 2016

    In 2016 "IFS" function is not working. "IFS" function is introduced after 2016.

     

    Error- =_xlfn.IFS($B$1=0,0,$I16<>$A16,RANDBETWEEN(B$3,B$4),TRUE,B16)

     

    Can you provide nested IF, Please

Resources