Hello I have been using a Mod or Formula that has been able to cover me for quite some time that takes the cost of the products that we sell and if it is above the next tens column by $1.00 then it rounds up to the next $99.00.
Here is what the formula looks like
=IF(AY3>=50,ROUNDUP(AY3/100,0)*100-1,ROUNDUP(AY3/100,0)*100-1)
Example: if our cost is $1901 which would be (AY3) then the Mod or formula rounds it up to $1999, we do not use cents in this price list. And I basically set it to give me that answer no matter what.
Now our company wants to change the way I have been doing it, however I have found nothing online that speaks to dollars, tens, or hundreds columns only cents to the next dollar.
They want the round up or down decision to be determined by whether the total dollar amount is $20 or more to go to the next highest $99, if it is $19 or less they want to round down to the next lowest $99, so in the above scenario of $1901, it would go down to $1899, if the total would have been $1920 then they want to go to $1999.
I would like to cover myself with an in between in case they still feel it is going to add to many dollars and want it to go to the next $49. Probably through a IF function.
Every total dollar resides in it's own cells, however the Price list is now over 9,000 total rows, so I will have to spend time to copy and paste the formula into. The cell where the formula would reside is not seen by anyone and reports to other operating cells in the price list area. If that helps.
It would be great if I could get the "round up or down to the next $99. and the ability to tell it to go the next $49." in the same formula or mod, I'm very familiar with formulas, but have a very limited understanding of Mods.
I also know some VBA, but only with working with Macros I have built into this same spreadsheet, so if you have a solution, can you be clear on how to install it whether a Mod or VBA Script.
Thanks in advance for the help.
Anthony
Here is what the formula looks like
=IF(AY3>=50,ROUNDUP(AY3/100,0)*100-1,ROUNDUP(AY3/100,0)*100-1)
Example: if our cost is $1901 which would be (AY3) then the Mod or formula rounds it up to $1999, we do not use cents in this price list. And I basically set it to give me that answer no matter what.
Now our company wants to change the way I have been doing it, however I have found nothing online that speaks to dollars, tens, or hundreds columns only cents to the next dollar.
They want the round up or down decision to be determined by whether the total dollar amount is $20 or more to go to the next highest $99, if it is $19 or less they want to round down to the next lowest $99, so in the above scenario of $1901, it would go down to $1899, if the total would have been $1920 then they want to go to $1999.
I would like to cover myself with an in between in case they still feel it is going to add to many dollars and want it to go to the next $49. Probably through a IF function.
Every total dollar resides in it's own cells, however the Price list is now over 9,000 total rows, so I will have to spend time to copy and paste the formula into. The cell where the formula would reside is not seen by anyone and reports to other operating cells in the price list area. If that helps.
It would be great if I could get the "round up or down to the next $99. and the ability to tell it to go the next $49." in the same formula or mod, I'm very familiar with formulas, but have a very limited understanding of Mods.
I also know some VBA, but only with working with Macros I have built into this same spreadsheet, so if you have a solution, can you be clear on how to install it whether a Mod or VBA Script.
Thanks in advance for the help.
Anthony
