Increase count by X when another cell increments by Y

[megera716]

I feel like this solution is right on the tip of my fingers but I can't make it work....

I'm working on some projections with potential revenues across the top.

There are always at least 2 of a particular employee position. For every $5M in revenue, I want to increase the count of that position by 2 employees (e.g., at $3M, we have 2, at $5M, we still have 2. At $10M, we should have 4) and then I'm multiplying the count of the people by $50,000 salary in C16.

At >=$5M revenue,

=MIN($C$16*2,(J3/5000000)*2*$C$16)

seems to be working. But at $3M and $4M, it returns $60,000 and $80,000 respectively (for $3M, it does (3000000/5000000) * 2 * 50,000 = 60,000.

I also tried

=IF(J3/5000000<1,2*$C$16,(J3/5000000)*2*$C$16)

, which works fine at 3-5M, but then at 6M, it's giving me $120,000.

 

[Joe4]

If I am understanding you correctly (and I may not be), maybe try inserting the INT function in there, i.e.

=MIN($C$16*2,INT(J3/5000000)*2*$C$16)

 

[megera716]

If I am understanding you correctly (and I may not be), maybe try inserting the INT function in there, i.e.

=MIN($C$16*2,INT(J3/5000000)*2*$C$16)

Now the $3M and $4M columns return $0 on this row and every revenue amount>=$5M returns $100K (even though $10M should be $200K).

 

[Joe4]

OK, then I think I do not understand the details of your question. What exactly is in cells J3 and C16?

Could you please post a few data examples of different scenarios, along with the expected results of each?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[megera716]

I think you did understand it, I'm just not familiar with the INT function so I don't know how to say why that's not working.

Row 3 is the possible revenues. They are every other column, starting in D3 (I just happened to be in cell J3 so I copied and pasted that formula).
C16 is the salary for this position

I'm actually using Google Sheets (boo hiss, but my boss is a diehard Google user) so I don't think the add-in will work

Purpose: forecast the labor cost for this position based on various revenues.

Goal: Every multiple of $5M in revenue, the count of people in this position should increase by 2. The minimum count is always 2.

Example scenarios:

• At $3M in revenue, I should have 2 of this position multiplied by C16 (which contains "50,000" as the salary as described in the OP) so cost = $100K
• At $5M in revenue, I still have 2 of the position.
• At $6M, I still have 2.
• At $10M. I will have 4 (the minimum 2, plus revenue has incremented by $5M so I need to add 2 people) so cost = $200K.

I have a bunch more positions to do this for but I know how to replace cell references as needed to accomplish that, I just can't get the framework to work right.

 

[Joe4]

Try this:

=((INT(MAX((J2-5000000),0)/5000000)*2)+2)*$C$16

 

[megera716]

Try this:

=((INT(MAX((J2-5000000),0)/5000000)*2)+2)*$C$16

AAAAH, perfect (I did just change the J2 to J3, I figured that's what you meant )!!!! THANK YOU SO, SOOOOOO MUCH

 

[Joe4]

You are welcome!

(I did just change the J2 to J3, I figured that's what you meant

Yes, sorry about the typo!