Forum Discussion

bvt_events's avatar
bvt_events
Copper Contributor
Feb 10, 2020

formula excel with fixed sharing options to zero

a good morning.

I had an excel question.

am creating an excel document where we can confuse LED screens with what do you need when you make this.

now we have fixed sizes of brackets of 1 meter and 0.5 meters but if we have a screen of 11.50 meter we want a formula that says you need 11 brackets of 1 meter and 1 of 0.50 how can I make this formula .

I would like to hear from you

Kind regards, Barry van Tunen

excel

2 Replies

  • PReagan's avatar
    PReagan
    Bronze Contributor
    Feb 10, 2020

    Hello bvt_events,

     

    There are many ways that this could be accomplished. Here is one method:

    For the amount of 1 meter brackets

    =QUOTIENT(A1,1)

     For the amount of 0.5 meter brackets

    =2*MOD(A1,1)
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Feb 10, 2020

    bvt_events 

    Assuming that your screens always are always sized by increments of half a meter, the number of meters will be

     

    =INT(size)

     

    and the half meters will be

     

    =IFERROR(0.5/(size-int(size),0)

or 

=IFERROR(0.5/MOD(size,1),0)

     

    But this last one will always return 1 or 0.

    The attached file has a simple example.

     

Resources