You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

User profile for user: Squirreldad
Squirreldad Author
User level: Level 1
4 points

create formulas in Numbers to calculate bonuses based on a fixed amount and day of the week

Hello, I’m trying to create formulas in Numbers to calculate bonuses based on a fixed amount and day of the week.


Monday-Thursday and Sunday bonuses start at $1000, which earns a $30 bonus. Every additional $50 in sales earns an extra $5 bonus.


Friday and Saturday bonuses start at $1800, which earns a $30 bonus. Every additional $50 in sales earns an extra $5 bonus.


So, for example, a bonus earned on Monday for $1350 in sales would be $65.


A bonus earned on Friday for $2000 in sales would be $50.


Can this be done with Numbers where the bonuses are calculated in a separate column automatically based on the amount sales I input and the day of the week? If so please show me how.

iPhone 15 Pro, iOS 18

Posted on Aug 8, 2024 1:26 PM

Reply
Question marked as Top-ranking reply
User profile for user: Yellowbox
Yellowbox
User level: Level 7
21,209 points

Posted on Aug 9, 2024 12:36 AM

Hi Squirreldad,


Here is one way. I have used several columns because my brain works best with small steps, rather than a single, complex formula. Also easier to debug. Hide the intermediate columns when all is working.



Enter dates in Column A and sales in column B.

Formula in C2 is DAYNAME(WEEKDAY(A2,1))

Formula in D2 is IF(OR(C2="Friday",C2="Saturday"),1800,1000)

Formula in E2 is IF(B2≥D2,30,0)

Formula in F2 is INT((B2−D2)÷10)

Formula in G2 is IF(F2<0,0,E2+F2)


Now hiding intermediate columns (C, D, E, F):



Regards,

Ian.

View in context
3 replies
Question marked as Top-ranking reply
User profile for user: Yellowbox
Yellowbox
User level: Level 7
21,209 points

Aug 9, 2024 12:36 AM in response to Squirreldad

Hi Squirreldad,


Here is one way. I have used several columns because my brain works best with small steps, rather than a single, complex formula. Also easier to debug. Hide the intermediate columns when all is working.



Enter dates in Column A and sales in column B.

Formula in C2 is DAYNAME(WEEKDAY(A2,1))

Formula in D2 is IF(OR(C2="Friday",C2="Saturday"),1800,1000)

Formula in E2 is IF(B2≥D2,30,0)

Formula in F2 is INT((B2−D2)÷10)

Formula in G2 is IF(F2<0,0,E2+F2)


Now hiding intermediate columns (C, D, E, F):



Regards,

Ian.

Reply
User profile for user: Squirreldad
Squirreldad Author
User level: Level 1
4 points

Aug 10, 2024 6:05 PM in response to Yellowbox

Thank you so much for the formulas and taking the time to reply. 


Is it possible to create a more precise rounding component in them? 


One issue I found is that the bonus amount is not rounded off to the $50 sales mark. Bonuses can only be earned in $5 increments. 


A $5 bonus is not earned until the sales are at a $50 or $100 mark; prorated bonuses are not earned. 


For example $1,040 in sales on Sun (1000 goal) would earn a $30 bonus instead of $34. The next bonus to earn would be $35 if sales totaled $1,050.

If sales totaled $1,060 a $35 bonus would still be the earned amount.


Please see picture of example using the formulas.

Reply

create formulas in Numbers to calculate bonuses based on a fixed amount and day of the week

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.
Learn more Sign up