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.

 

[excelisfunny]

Sorry, I forgot to mention for the last part.

I'm trying to make this as clean as possible. There's already 3 rows that show the amount of views for each page, 3 rows that show the amount of likes for each page, and 3 rows that show authors for each page. So having the conditional calculation for each would just take up another 3 rows, if it can be condensed into one long equation for each. If I split it up, then that'd be really messy.

So, I still need 3 final calculations, but each needs to be 1 row as the other info (views, likes, authors) is already defined. I tried my hand at this, by splitting each calculation into 3 parts, but then got lost.

 

[Michael M]

This should solve part 2

=IF($O$66<29; $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; "")))))

 

[excelisfunny]

This should solve part 2

=IF($O$66<29; $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; "")))))

Thank you! I believe it does. It seems I was closer than I thought!

 

[excelisfunny]

For part 3:

I think I'm close on this.

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

It seems to work when I put O18 has 5000 or above, but not if I put it as 10000 or above. Maybe I'm messing up again.

For the crazy part:

I think I have no idea on how to concatenate all 3 of the equations together

 

[Michael M]

Try using

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

 

[excelisfunny]

Thank you! It works :D

 

[excelisfunny]

Is it possible to put all three of these calculations all together? That way I don't have to do 9 rows to calculate (3 different calculations for 1 page, multiplied by 3 pages). Instead, it could just be 3.

 

[steve the fish]

You say:

divided by the amount of authors on the page as shown in O67

so id expect a number (eg 3) in O67. But you also say:

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

so id expect some initials in O67. Which is it?

 

[excelisfunny]

AFJ refers to three different authors, with the initials of A, F, and J. So I've set it up like "If O67 has AFJ, divide by the bonus in 100 by 3, if it has AJ, divide by 2, if it has FJ, divide by 2, if it has S, divide by 1". This is because the bonus is a set value, and authors will fluctuate.

Now it seems that all 3 pieces of code are finished and working as intended. But I wish to have them all on one line, so I don't need to have 3 sets of calculations for each webpage. Instead, it could be one for each webpage, I think.