Any Exel gurus here? (I am not at all)

HornheaDD

Viewpoint Vigilante
Joined
Mar 22, 2016
Posts
2,789
I'm no Excel master, so I apologize if this seems really easy but I honestly don't exactly know how to word my question to where Google will know WTF I'm talking about.

Here's my situation. I have an Excel sheet I'm trying to set up as a personal goal tracker for my fellow employees. The incentive program we have instituted will grant an employee $5.00 for each item completed, past 25, in the month.

So basically, if Billy completes 25 goals, he gets no bonus. But if he completes 26, He gets five bucks. At 27, another $5, and so on. $5 for each additional goal completed after 25.

I have my excel sheet using =counta(A1:A100) and so on to count/display how many goals an employee has completed in a month and its working great. What I would like to do is have another cell next to that, that will display how much an employee will get as a bonus. But I'd like to format it to where it will only display how much they've made and increase by $5 with each new goal they add to the column after 25 goals. If they have completed 25, its blank (or 0, whatever). But if they complete say, 27, the cell displays $10.00. 28 = $15, etc etc etc.

I hope that makes sense, and I apologize if it's really simple, but I just dont know where to start to get this formula, if it's even possible. As an example, I've attached an image with what I would like the sheet to display.


Please halp.
 

neo_moe

Been There., Done That., It Was Shit.,
15 Year Member
Joined
Apr 20, 2007
Posts
8,637
I think I get it. Maybe the logic would be something like ..

If the value of (counta - 25) > 0, then multiply that resulting value by 5, otherwise blank.

Does that make sense? I’m putting the kids to bed right now but can try and work out the actual formula if you need help.
 

GohanX

Hollywood Hulk Gohan
20 Year Member
Joined
Sep 28, 2001
Posts
11,801
Fuck, I could figure this out if I was on my work computer. I have similar formulas on my billing spreadsheet, but I'm at home and won't be back in the office for over a week. There is syntax to make the formula only display something over 25, and also to show zero if it's less than 26, but I couldn't tell you off the top of my head.
 

HornheaDD

Viewpoint Vigilante
Joined
Mar 22, 2016
Posts
2,789
Its all good fellas, I called my cousin, and he confused the FUCK out of me, so he asked me to just send him my spreadsheet and he fixed it lol. But while he was doing that, the wife actually figured it out as well, so I'm all good!

Now to add a clear data button...
 

ForeverSublime

6400|!!|Kyo Clone
20 Year Member
Joined
Oct 23, 2001
Posts
6,416
I think I get it. Maybe the logic would be something like ..

If the value of (counta - 25) > 0, then multiply that resulting value by 5, otherwise blank.

Does that make sense? I’m putting the kids to bed right now but can try and work out the actual formula if you need help.

Moe's suggestion can work. In what I'm imagining is cell Q2, type the following, then drag the handle down however many cells you'd like

=IF(P2>25,5 * (P2 - 25),"")

You can highlight the Q column, right click a cell and select format. . . on the Number tab, choose Currency and where it says "Decimal places:" type 0

Edit:

Its all good fellas, I called my cousin, and he confused the FUCK out of me, so he asked me to just send him my spreadsheet and he fixed it lol. But while he was doing that, the wife actually figured it out as well, so I'm all good!

Now to add a clear data button...

Do you really need a clear data button? Alternatively, if you highlight cells in column A and hit delete, then retype, column P and subsequently column Q formulas should automatically calculate. If not, it's a setting under File->Options->Formulas
 
Last edited:

HornheaDD

Viewpoint Vigilante
Joined
Mar 22, 2016
Posts
2,789
Moe's suggestion can work. In what I'm imagining is cell Q2, type the following, then drag the handle down however many cells you'd like

=IF(P2>25,5 * (P2 - 25),"")

You can highlight the Q column, right click a cell and select format. . . on the Number tab, choose Currency and where it says "Decimal places:" type 0

Edit:



Do you really need a clear data button? Alternatively, if you highlight cells in column A and hit delete, then retype, column P and subsequently column Q formulas should automatically calculate. If not, it's a setting under File->Options->Formulas
Well just a clear button in the sense of resetting the data for the next month. What will go in there are just reference numbers that only serve to fill the cells. At the end of the month they are useless to us, so we can just wipe the slate clean.

I found a super simple button config/macro to get it done.
 

norton9478

So Many Posts
No Time
For Games.
15 Year Member
Joined
Oct 30, 2003
Posts
33,774
What foreversublime said.
 
Last edited:

neo_moe

Been There., Done That., It Was Shit.,
15 Year Member
Joined
Apr 20, 2007
Posts
8,637
See if this works for column N

5*((COUNTA(N:N))-25)

I dont think that works. For example, if someone did 23 tasks, instead of getting no incentive...wouldnt that formula result in a negative $10?
 

norton9478

So Many Posts
No Time
For Games.
15 Year Member
Joined
Oct 30, 2003
Posts
33,774
I dont think that works. For example, if someone did 23 tasks, instead of getting no incentive...wouldnt that formula result in a negative $10?

Yeah, I left of the If statement, then read forever sublime's post.
 

Neodogg

Dogg-Father,
20 Year Member
Joined
Nov 27, 2002
Posts
4,423
Just make a new tab for each month, copy/paste
That way you’ll have the years data and can make perdy grafxz
 
Top