Need some help. Might be on the right track, but feeling overwhelmed

[excelisfunny]

Hi everyone! I really debated posting here or not, as my question may be quite silly!

Here's what I'm trying to do:

I'm calculating a salary bonus, based on daily views of 3 different webpages. The pages change everyday, in the same fashion as a news article, and can have 3 different writers.

For example: "If webpage 1 has views of 5000, and is written by AFJ, allocate a bonus of 100 and then divide the bonus by 3 as there is 3 authors. Then if the page's likes are above 30, give an extra bonus of 1.5x the final divided bonus to each."

So theres 3 steps, that I'm trying to calculate.

Part 1 - column O65:

If webpage author = AFJ, then divide the bonus by 3, if its FJ then divide it by 2, and if its S then divide it by 1.

O65's equation states:
=IF(O67="AFJ";100/3;IF(O67="FJ";100/2;IF(O67="S";50/1;0)))

This seems fine and works as intended.

Part 2 - column O70:

If the likes on the page (as written in O66) are equal or greater than 30 but less than 49, mutiply the number as shown in O65 by 1.5; if the likes on the page are equal to or greater than 50 but less than 99, muliply the number shown in O65 by 2; if the likes on the page are equal to or greater than 100, multiply the number shown in O65 by 3; if the likes on the page are equal to or less 29, do nothing to the number.

=IF($O$66=30<49; $O$65*1.5;IF($O$66=50<99;$O$65*2;IF($O$66>=100;$O$65*3;IF($O$66=0<29; $O$65*1; ""))))

This doesn't work, and I'm a bit lost on what I need to do.

Part 3 - column O71:

The XLS file has 3 rows that show the numbers of views in the 3 webpages. These are O17, O18, O19.

If the amount of views in webpage 1 is above 5000, then allocate a bonus of 100 divided by the amount of authors on the page as shown in O67. If the views are above 10000 then allocate a bonus of 150 divided by the amount of authors on the page as shown in O67.


The crazy part:

Then, rather than having 3 rows of different calculations, it would be better if its all in one row, as one calculation. This is because I already have 3 rows for the amount of writers on each page, as well as 3 rows for the views on each webpage, and 3 rows for the amount of likes. The calculation would also not give any likes bonus to an article if the condition of minimum views for the page is not more than 5000. (For example, if a page has 30 likes, but the views are 3000, then no bonus is given, so no bonus can be multiplied by 1.5 for the 30 likes).

For example:

Page 1 Views: 5000
Page 1 Writers: AFJ
Page 1 Likes: 30
Bonus: 100 * 1.5 / 3 =

Page 2 views: 10000
Page 2 Writers: FJ
Page 2 likes: 50
Bonus: 150 * 2 / 2 =

Page 3 views: 2000
Page 3 writers: S
Page 3 likes: 50
Bonus: 0

I hope you all can understand! Sorry for the crazy explanation.

 

[Michael M]

So what are the 3 calculations your are currently calling "working"

 

[excelisfunny]

1) =IF(O67="AFJ";100/3;IF(O67="FJ";100/2;IF(O67="S";50/1;0)))

2) =IF($O$18>=10000;150;IF($O$18>=5000;100;0))

3) =IF($O$66<30;$O$65*1;IF($O$66<49;$O$65*1.5;IF($O$66<99;$O$65*2;IF($O$66>=100;$O$65*3;IF($O$66=0<29;$O$65*1;"")))))


The first divides a bonus based on the authors/amount of authors. The second calculates the amount of bonus (100 for 5000 views, 150 for 10000 views). The third calculates the bonus multiplier based on how many likes the page gets (less than 30 likes = no bonus multiplier, more than 30 likes = 1.5x bonus, etc).

Taking another look at it, I think the first calculation should reference the second so that its not a static number, and is instead based on the actual views and how much bonus they can get.

So =IF(O67="AFJ";$O$18/3;IF(O67="FJ";$O$18/2;IF(O67="S";$O$18/1;0))) , I think.

What I'm aiming for is for all 3 of these calculations to be in one calculation, as I also need to do these 3 calculations for the other 2 pages too (O19, O20).

 

[Michael M]

To combine the first 2 formulas, you are going to have 6 variables...do you really want to go that way ??
In this example you provided.....
=IF(O67="AFJ";$O$18/3;IF(O67="FJ";$O$18/2;IF(O67="S";$O$18/1;0)))
you don't take into account for whether O18 is >5000, or >10000
From my perspective, I think you will nee more than one formula !