Jump to content
Search In
  • More options...
Find results that contain...
Find results in...

Need help with Google spreadsheets

Go to solution Solved by Dean0919,

Problem got fixed. Seems like this is the formula I needed: =COUNT(B$5:B)/COUNTA($A$5:$A)

Hello,

I need some help with spreadsheet, but before I write about my problem, you need to understand what is it about:

 

I'm playing some text based web browser game, where there are monsters that you can summon and the kill them by dealing them damage and other players can also join you killing that monster. Monsters drop some gear and other in game items. I decided to make a spreadsheet where I put the information what kind of damage I dealt to the monster and what items it dropped and then sheet is calculating what is the chance (parentage) of each item. Somehow, by browsing others sheets I managed to copy paste few formulas and almost made my spreadsheet perfect, well almost. Now here's the thing: I managed to find out formulas and my sheet is telling me chances of monster gear drops, but I also want to have same formula for another drop which isn't gear, but a game currency coin. I don't know what kind of formula do I need for it. I will post the copy of my sheet here now:

 

https://docs.google.com/spreadsheets/d/159H2SgHN0HrfHbM7ZVAaMJJV1uvwUUjBsuVcUOLQxdw/edit?usp=sharing

 

I wanna know what is the chance of "favor points" drops here. There are in total 24 favor points dropped in this monster currently and there is 29 tests done in total. Can anyone help me to figure out the % of chance of favor points in here?

If I calculated correctly with my bad math, answer is 82%, but how do I make formula of that in that sheet?

 

 

CPU: i7 3770k 3.9GHz | COOLER: Arctic Freezer 34 eSports | GPU: GIGABYTE GTX 1070 G1 Gaming 8GB | MOTHERBOARD: MSI Z77A-G45 GAMING | RAM: G.Skill Trident X 16GB | STORAGE: Crucial M550 SSD 120GB + 2TB HDD + 4TB HDD +4TB HDD | CASE: Cooler Master HAF 912 Plus | PSU: XFX PRO1050W Black Edition (80+ Gold) | KEYBOARD: Corsair Vengeance K70 | MOUSE: Logitech G502 | MOUSEPAD: Marvo MG010 | MONITOR: Philips 298P4 29" UltraWide | SPEAKERS: Microlab Solo7C | HEADSET: Trust GXT 410 Rune Illuminated | OS: WIndows 10 64bit
Link to post
Share on other sites

If you want to calculate a percentage of a full amount (i.e. how much % is 24 of 29) you can use:

Part / Whole * 100 = percentage.

 

i.e. if I want to calculate how much 10 is of 50, I can already tell 10 fits into 50 five times, so it's 20%.

10 / 50 = 0.2 * 100 = 20%

For reasons, it can be preferable to calculate the percentage in a decimal amount (like 0.2), but if you just want the percentage, you need the *100 at the end.

 

From what I am seeing, column A is the test you ran, column B is the favor points you got right?

You can calculate a couple different things from this:

- How often do you get any favor point at all?
- How often do you get a certain number of favor points? (since I see 1 or 2 favor points)

- What the is the average/median/modus amount of favor points?

 

These are all different sort of statistics you might want, so you have to think about that first.

 

Just to give an example for "how often do you get any favor points [vs. not getting favor points]?" it's this:

=VALUE(B3 / COUNT(A5:A) *100)

This takes

- B3 (the favor points you have)

- divides it by COUNT(A5:A) (the amount of tests you have done, so it only counts where you have a date filled in)

- multiplies it by 100, for the percentage.

 

That indeed gets you around 83% (82.76%).

But that does not account at all the difference between getting 1 or 2 favor points.

"We're all in this together, might as well be friends" Tom, Toonami.

Sorry if my post seemed rude, that is never my intention.

"Why do we suffer a lifetime for a moment of happiness?" - Anonymous

Link to post
Share on other sites
6 minutes ago, minibois said:

If you want to calculate a percentage of a full amount (i.e. how much % is 24 of 29) you can use:

Part / Whole * 100 = percentage.

 

i.e. if I want to calculate how much 10 is of 50, I can already tell 10 fits into 50 five times, so it's 20%.

10 / 50 = 0.2 * 100 = 20%

For reasons, it can be preferable to calculate the percentage in a decimal amount (like 0.2), but if you just want the percentage, you need the *100 at the end.

 

From what I am seeing, column A is the test you ran, column B is the favor points you got right?

You can calculate a couple different things from this:

- How often do you get any favor point at all?
- How often do you get a certain number of favor points? (since I see 1 or 2 favor points)

- What the is the average/median/modus amount of favor points?

 

These are all different sort of statistics you might want, so you have to think about that first.

 

Just to give an example for "how often do you get any favor points [vs. not getting favor points]?" it's this:


=VALUE(B3 / COUNT(A5:A) *100)

This takes

- B3 (the favor points you have)

- divides it by COUNT(A5:A) (the amount of tests you have done, so it only counts where you have a date filled in)

- multiplies it by 100, for the percentage.

 

That indeed gets you around 83% (82.76%).

But that does not account at all the difference between getting 1 or 2 favor points.

Yes, I want to know How often do you get any favor point at all?
Thank you, my math is bad, but I think I understand what you explained to me. Now, I have one more question. How do I put that number in percentage? I mean instead of  82.75862069 how I can make it appear in sheet like this: 82%?

CPU: i7 3770k 3.9GHz | COOLER: Arctic Freezer 34 eSports | GPU: GIGABYTE GTX 1070 G1 Gaming 8GB | MOTHERBOARD: MSI Z77A-G45 GAMING | RAM: G.Skill Trident X 16GB | STORAGE: Crucial M550 SSD 120GB + 2TB HDD + 4TB HDD +4TB HDD | CASE: Cooler Master HAF 912 Plus | PSU: XFX PRO1050W Black Edition (80+ Gold) | KEYBOARD: Corsair Vengeance K70 | MOUSE: Logitech G502 | MOUSEPAD: Marvo MG010 | MONITOR: Philips 298P4 29" UltraWide | SPEAKERS: Microlab Solo7C | HEADSET: Trust GXT 410 Rune Illuminated | OS: WIndows 10 64bit
Link to post
Share on other sites
3 minutes ago, Dean0919 said:

Yes, I want to know How often do you get any favor point at all?
Thank you, my math is bad, but I think I understand what you explained to me. Now, I have one more question. How do I put that number in percentage? I mean instead of  82.75862069 how I can make it appear in sheet like this: 82%?

Format the cell as a percentage. This might mess up your numbers and you might have to take away the *100 in your formula. I forget exactly how Sheets handles it.

Quote me to see my reply!

SPECS:

CPU: Xeon X5650 OC'd to 4.2GHz @ 1.35V (courtesy of @XR6)Motherboard: Asus Sabertooth X58 RAM: 6x4GB G.Skill Ripjaws X GPU: Asus RX 570 Strix Storage: WD Blue 1TB and a 128GB Kingston UV400 PSU: EVGA 600B Case: Fractal Design Define C Cooling: H100i V2, be quiet! Pure Wings 2 (two intake, two exhausting through radiator) Monitor: 3x Dell P2210 on a Steelcase Eyesite triple monitor stand Mouse: Logitech MX Master 3 Keyboard: It changes, but usually Focus FK-9000 Mousepad: Steelseries QcK XL Headphones:  Sennheiser HD598SE and Fiio FH1S

 

 

 

 

i use arch btw

Link to post
Share on other sites
1 minute ago, kelvinhall05 said:

Format the sell as a percentage. This might mess up your numbers and you might have to take away the *100 in your formula. I forget exactly how Sheets handles it.

That's it. It worked.

CPU: i7 3770k 3.9GHz | COOLER: Arctic Freezer 34 eSports | GPU: GIGABYTE GTX 1070 G1 Gaming 8GB | MOTHERBOARD: MSI Z77A-G45 GAMING | RAM: G.Skill Trident X 16GB | STORAGE: Crucial M550 SSD 120GB + 2TB HDD + 4TB HDD +4TB HDD | CASE: Cooler Master HAF 912 Plus | PSU: XFX PRO1050W Black Edition (80+ Gold) | KEYBOARD: Corsair Vengeance K70 | MOUSE: Logitech G502 | MOUSEPAD: Marvo MG010 | MONITOR: Philips 298P4 29" UltraWide | SPEAKERS: Microlab Solo7C | HEADSET: Trust GXT 410 Rune Illuminated | OS: WIndows 10 64bit
Link to post
Share on other sites

miniboiskelvinhall05

Wow, you guys are brilliant. Thank you very much for figuring this out. I appreciate it very much. You guys rock!

CPU: i7 3770k 3.9GHz | COOLER: Arctic Freezer 34 eSports | GPU: GIGABYTE GTX 1070 G1 Gaming 8GB | MOTHERBOARD: MSI Z77A-G45 GAMING | RAM: G.Skill Trident X 16GB | STORAGE: Crucial M550 SSD 120GB + 2TB HDD + 4TB HDD +4TB HDD | CASE: Cooler Master HAF 912 Plus | PSU: XFX PRO1050W Black Edition (80+ Gold) | KEYBOARD: Corsair Vengeance K70 | MOUSE: Logitech G502 | MOUSEPAD: Marvo MG010 | MONITOR: Philips 298P4 29" UltraWide | SPEAKERS: Microlab Solo7C | HEADSET: Trust GXT 410 Rune Illuminated | OS: WIndows 10 64bit
Link to post
Share on other sites
Just now, Dean0919 said:

Yes, I want to know How often do you get any favor point at all?
Thank you, my math is bad, but I think I understand what you explained to me. Now, I have one more question. How do I put that number in percentage? I mean instead of  82.75862069 how I can make it appear in sheet like this: 82%?

After testing some stuff out, it's better to do this:

Use this formula:

=ROUND(B3 / COUNT(A5:A), 2)

(Difference is the lack of *100 and the 2 at the end makes it so 0.8278... etc. will become 0.83)

Now go to Format > Number > Percentage and it will format to 83%.

In my case it formatted to 83.00%, if that happens to you just go to Format > Number > More Formats > Custom Format > 0%

"We're all in this together, might as well be friends" Tom, Toonami.

Sorry if my post seemed rude, that is never my intention.

"Why do we suffer a lifetime for a moment of happiness?" - Anonymous

Link to post
Share on other sites

Sorry to bother you again kelvinhall05, minibois, but I just tested this formula in another sheet where I have small number of tests and I noticed that this formula also counts of amounts of favor points per run, for example here it counted as a 50%. I ran only 6 tests there and only in 2 runs appeared favor points. This can't be 50%.

 

I want to count not the amount of favor points per run of test, but the chance if favor point appears at all in a test run or no, not their amount. 

CPU: i7 3770k 3.9GHz | COOLER: Arctic Freezer 34 eSports | GPU: GIGABYTE GTX 1070 G1 Gaming 8GB | MOTHERBOARD: MSI Z77A-G45 GAMING | RAM: G.Skill Trident X 16GB | STORAGE: Crucial M550 SSD 120GB + 2TB HDD + 4TB HDD +4TB HDD | CASE: Cooler Master HAF 912 Plus | PSU: XFX PRO1050W Black Edition (80+ Gold) | KEYBOARD: Corsair Vengeance K70 | MOUSE: Logitech G502 | MOUSEPAD: Marvo MG010 | MONITOR: Philips 298P4 29" UltraWide | SPEAKERS: Microlab Solo7C | HEADSET: Trust GXT 410 Rune Illuminated | OS: WIndows 10 64bit
Link to post
Share on other sites

bump

CPU: i7 3770k 3.9GHz | COOLER: Arctic Freezer 34 eSports | GPU: GIGABYTE GTX 1070 G1 Gaming 8GB | MOTHERBOARD: MSI Z77A-G45 GAMING | RAM: G.Skill Trident X 16GB | STORAGE: Crucial M550 SSD 120GB + 2TB HDD + 4TB HDD +4TB HDD | CASE: Cooler Master HAF 912 Plus | PSU: XFX PRO1050W Black Edition (80+ Gold) | KEYBOARD: Corsair Vengeance K70 | MOUSE: Logitech G502 | MOUSEPAD: Marvo MG010 | MONITOR: Philips 298P4 29" UltraWide | SPEAKERS: Microlab Solo7C | HEADSET: Trust GXT 410 Rune Illuminated | OS: WIndows 10 64bit
Link to post
Share on other sites
23 hours ago, Dean0919 said:

bump

Your mentions aren't working. You need to select correct members from the list @Dean0919

Or quote them.

^^^^ That's my post ^^^^
<-- This is me --- That's your scrollbar -->
vvvv Who's there? vvvv

Link to post
Share on other sites

Problem got fixed. Seems like this is the formula I needed: =COUNT(B$5:B)/COUNTA($A$5:$A)

CPU: i7 3770k 3.9GHz | COOLER: Arctic Freezer 34 eSports | GPU: GIGABYTE GTX 1070 G1 Gaming 8GB | MOTHERBOARD: MSI Z77A-G45 GAMING | RAM: G.Skill Trident X 16GB | STORAGE: Crucial M550 SSD 120GB + 2TB HDD + 4TB HDD +4TB HDD | CASE: Cooler Master HAF 912 Plus | PSU: XFX PRO1050W Black Edition (80+ Gold) | KEYBOARD: Corsair Vengeance K70 | MOUSE: Logitech G502 | MOUSEPAD: Marvo MG010 | MONITOR: Philips 298P4 29" UltraWide | SPEAKERS: Microlab Solo7C | HEADSET: Trust GXT 410 Rune Illuminated | OS: WIndows 10 64bit
Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×