Excel/Sheets Divide and Round Up

[b_ondoptical]

I'm working on a store inventory list in order to import everything onto WooCommerce all at once.  The store wants to fill both the regular price and sale price fields with all items being around half off.  I've used =(x/2) so it automatically divides by two.  Thing is, this puts out some weird prices like $22.50, $37.50, etc.  I'd like to do make is so that the price rounds out to the nearest 5 (e.g. if half off is 22.50, round up to 24.99.)  Is there a way to program the Round function to do this or am I going about this the wrong way altogether?  Just trying to avoid inputting all the sale prices manually.

[ddennis002]

11 minutes ago, b_ondoptical said:

I'm working on a store inventory list in order to import everything onto WooCommerce all at once.  The store wants to fill both the regular price and sale price fields with all items being around half off.  I've used =(x/y) with y column just filled with "2" so it automatically divides by two.  Thing is, this puts out some weird prices like $22.50, $37.50, etc.  I'd like to do make is so that the price rounds out to the nearest 5 (e.g. if half off is 22.50, round up to 24.99.)  Is there a way to program the Round function to do this or am I going about this the wrong way altogether?  Just trying to avoid inputting all the sale prices manually.

What is the highest and lowest priced item?

[b_ondoptical]

Just now, ddennis002 said:

What is the highest and lowest priced item?

It's a bunch of random; I haven't input everything yet so I don't know.  I think =ROUND(x/2)+.99 is closer to where I want to be and I've settled for using that for now although $100.99 looks a little funny when it pops up.

[ddennis002]

15 minutes ago, b_ondoptical said:

I'm working on a store inventory list in order to import everything onto WooCommerce all at once.  The store wants to fill both the regular price and sale price fields with all items being around half off.  I've used =(x/y) with y column just filled with "2" so it automatically divides by two.  Thing is, this puts out some weird prices like $22.50, $37.50, etc.  I'd like to do make is so that the price rounds out to the nearest 5 (e.g. if half off is 22.50, round up to 24.99.)  Is there a way to program the Round function to do this or am I going about this the wrong way altogether?  Just trying to avoid inputting all the sale prices manually.

Think this would work a bit better?

 

=ROUNDUP(X/2,-1)-0.01

[mariushm]

You could use MOD and QUOTIENT functions to get the fractions and whatnot

 

MOD(CELL,1) will give you the part after the dot ... ex  =MOD(24.5,1) = 0.5

=QUOTIENT(CELL,1) will give you the integer number

 

so you could write something like this:

 

=IF(MOD(CELL,1)=0.5,QUOTIENT(CELL,1)+0.49,CELL)

 

To change number to  [number-1].99  ex 25 becomes 24.99 , you could say something like

 

=IF(MOD(CELL,1)=0,CELL-0.01,CELL)

 

You can place an IF inside another IF , something like

 

=IF(MOD(CELL,1)=0,CELL-0.01,  IF(MOD(CELL,1)=0.5,QUOTIENT(CELL,1)+0.49,CELL)  )

 

You may also want to check out the CEILING function: https://exceljet.net/excel-functions/excel-ceiling-function

 

=CEILING(CELL,0.49)  will convert 24.51.. 24.98 to 24.99

 

Other mathematical functions (LibreCalc documentation but they should be the same in Excel) : https://help.libreoffice.org/Calc/Mathematical_Functions

 

[b_ondoptical]

20 minutes ago, ddennis002 said:

Think this would work a bit better?

 

=ROUNDUP(X/2,-1)-0.01

This rounds to the nearest 9.99, which puts prices a little higher than intended.  Trying not to go that much over the half off price and definitely not under.

19 minutes ago, mariushm said:

You could use MOD and QUOTIENT functions to get the fractions and whatnot

 

MOD(CELL,1) will give you the part after the dot ... ex  =MOD(24.5,1) = 0.5

=QUOTIENT(CELL,1) will give you the integer number

 

so you could write something like this:

 

=IF(MOD(CELL,1)=0.5,QUOTIENT(CELL,1)+0.49,CELL)

 

To change number to  [number-1].99  ex 25 becomes 24.99 , you could say something like

 

=IF(MOD(CELL,1)=0,CELL-0.01,CELL)

 

You can place an IF inside another IF , something like

 

=IF(MOD(CELL,1)=0,CELL-0.01,  IF(MOD(CELL,1)=0.5,QUOTIENT(CELL,1)+0.49,CELL)  )

 

You may also want to check out the CEILING function: https://exceljet.net/excel-functions/excel-ceiling-function

 

=CEILING(CELL,0.49)  will convert 24.51.. 24.98 to 24.99

 

Other mathematical functions (LibreCalc documentation but they should be the same in Excel) : https://help.libreoffice.org/Calc/Mathematical_Functions

 

O_O  My last Excel certification was for '07...  Can you use smaller functions? xDDD

[mariushm]

It looks scary but it's actually quite simple..

 

IF  has three parts :   IF (  statement ,  do this if true,  do this if false )

 

So for example  = IF ( CELL = 10 ,  "YES" , "NO" )    means content of cell with this formula becomes  "YES" if the contents of CELL is 10,  "NO" otherwise

 

So now I can use the MOD function to get the remainder of the value divided by 1 ...  remainder of 24.5 divided by 1 will be 0.5  , remainder of 10.1 divided by 1 will be 0.1 and so on.

So now I can say  IF  (remainder of number divided by 1  is 0.5 ,  do the stuff for when number ends with 0.50 , do the stuff for when number ends with anything else )

if ( remainder of cell divided by 1 is 0.5 ,  

     [THEN PART ] put the value of cell minus 0.01 to get .49 at the end , 

     [ELSE PART] just store the original number because i'm not changing anything )

 

[b_ondoptical]

12 minutes ago, mariushm said:

It looks scary but it's actually quite simple..

 

IF  has three parts :   IF (  statement ,  do this if true,  do this if false )

 

So for example  = IF ( CELL = 10 ,  "YES" , "NO" )    means content of cell with this formula becomes  "YES" if the contents of CELL is 10,  "NO" otherwise

 

So now I can use the MOD function to get the remainder of the value divided by 1 ...  remainder of 24.5 divided by 1 will be 0.5  , remainder of 10.1 divided by 1 will be 0.1 and so on.

So now I can say  IF  (remainder of number divided by 1  is 0.5 ,  do the stuff for when number ends with 0.50 , do the stuff for when number ends with anything else )

if ( remainder of cell divided by 1 is 0.5 ,  

     [THEN PART ] put the value of cell minus 0.01 to get .49 at the end , 

     [ELSE PART] just store the original number because i'm not changing anything )

 

That makes more sense.

 

[b_ondoptical]

So this is what I'm going with.

 

=ROUNDUP(G2/2)+0.99

Divides by 2, rounds up, and adds .99 so I get that wonderful psychological satisfaction of it being less than whatever big number is a penny away.  Thanks everyone!