Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Need to know how to change commission deducted from 10% in 2022 to 13% in 2023

Copper Contributor

I volunteer for a not-for-profit that sells products in a local flea mall. Our inventory will not change much when the year changes, but the commission charged by the flea mall will go from the current 10% to 13% in 2023. I would like to change the amount deducted from our gross to 13% after 1/1/2023 without changing the previous 10% deducted for this year. In other words, I would like to have the spreadsheet be ongoing, so that we can see the ongoing total net for each product. The formula I am using right now is   =Sales minus Cost - (Sales minus Cost * 0.1). Hope this makes sense! Any help is greatly appreciated.

2 Replies

@KEVance318 That makes some sense, but some things are not clear.

  1. Are you using an Excel table, or by "table" do you mean a range of rows and columns?  I have to ask as the syntax you showed for your Net calculation is not valid.
  2. Does a row represent an individual sale, or an accumulation of sales (e.g., by week or month or other time period)?
  3. Do you have a column that contains the relevant year or date?
  4. Are you permitted to add columns?  It seems that you should have a Commission column, and the 10% or 13% value should go there (only), where rounding can be handled properly, to satisfy the bean counters.
I appreciate you taking the time to clarify; I will do my best, but I actually googled what a table is, and the internet responses made me laugh out loud! I am permitted to add whatever I want, because I am the only one tracking this info; I am the sole bean counter, although there are many other folks who appreciate me trying to get this right. Google searches helped me write all of the formulas. I'm not sure about the syntax; I was just happy that the formulas were doing what they were supposed t do (I did check manually). Here is how this came about: I simply opened a new "workbook" in Excel. Every row represents a product that we sell. Each column is info we track: number of items purchased, number of items in inventory, cost of each individual item, sales price, number sold (which has a formula to update as the numbers in inventory decrease), sales to date in dollars (which has a formula to subtract cost of sold item from sales price), sales in dollars minus 10% commission. So, if I change the 10% to 13% for the items sold, it will change the net historically, when I want to keep 10% up until December 2022. So, I am assuming that you can write an "if before this date, then this happens," "if after this date, this happens" type of formula. My Google searches have let me down here. I am sure not knowing the terminology was part of the problem. Did I mention I was an English/Theatre Arts/Counseling major? Thank you for your help. P. S. Please explain the purpose of a column for relevant year or date. We don't actually have a fiscal year for this endeavor. Should I make that happen?