| Začetnik |
Tema: Help with excel formula |
17 odgovora
|
|
|
|
#1 objavljeno 16 Ruj 2019, 18:13:51
|
Citat
|
Hey all,
I'm not sure where to post this but i'm hoping someone might be able to help with this issue i'm having.
I'm not sure what is going wrong with this formula but when there is 0 CT added the calculations are correct but as soon as i add anything to the CT field, numbers are way off.
I'm ashamed to admit i'm a total noob at excel but any assistance would be deeply appreciated.
https://prntscr.com/p6vgng
CT added
https://prntscr.com/p6vgqa
|
|
|
|
|
|
#2 objavljeno 16 Ruj 2019, 18:21:17
|
Citat
|
I think the 2nd VLOOKUP takes the value from the wrong column in the lookup table. So this bit
should be changed to:
(1 becomes 2)
|
|
|
|
|
|
#3 objavljeno 16 Ruj 2019, 18:22:42
|
Citat
|
You need to bracket the h1*m1 or you are doing the whole formula to the power of 10
|
|
|
|
|
|
#4 objavljeno 16 Ruj 2019, 18:22:54
|
Citat
|
Quote ( Jasper Coosemans @ September 16th 2019,18:21:17 ) I think the 2nd VLOOKUP takes the value from the wrong column in the lookup table. So this bit
VLOOKUP(B2,M4:N12,1,0)
should be changed to:
VLOOKUP(B2,M4:N12,2,0)
(1 becomes 2)
I owe you a bottle of Whisky Jasper, thank you so much!
|
|
|
|
|
|
#5 objavljeno 16 Ruj 2019, 23:00:57
|
Citat
|
In Russian-speaking teams there are many drink-lovers, as well as mathematically literate people. Brad, the next time you will be in difficulty and will offer another bottle of Whisky, just let me know. I will find you a couple of such players :) ))))
|
|
|
|
|
|
#6 objavljeno 17 Ruj 2019, 00:14:49
|
Citat
|
Quote ( Serg Egorov @ September 16th 2019,23:00:57 ) In Russian-speaking teams there are many drink-lovers, as well as mathematically literate people. Brad, the next time you will be in difficulty and will offer another bottle of Whisky, just let me know. I will find you a couple of such players :) ))))
just as well Latvia has tons of the good stuff!
|
|
|
|
|
|
#7 objavljeno 27 Ruj 2019, 19:45:57
|
Citat
|
Sorry to bother you all but I have another question which I hope someone here may be able to help with.
Is there anyway I can automatically pull the next few races data from GPRO to excel?
I'm trying to do a car wear spreadsheet but wanted the sheet to be able to automatically update the next few tracks.... if possible.
|
|
|
|
|
|
#8 objavljeno 27 Ruj 2019, 19:55:29
|
Citat
|
Quote ( Brad Park @ September 27th 2019,19:45:57 ) Sorry to bother you all but I have another question which I hope someone here may be able to help with.
Is there anyway I can automatically pull the next few races data from GPRO to excel?
I'm trying to do a car wear spreadsheet but wanted the sheet to be able to automatically update the next few tracks.... if possible.
The good news is yes there is, the bad news I've no clue how that is done and expect it to be FOBY anyway...
Other simple solution:
Make a simple calender table in excel like
7209 Sochi
7210 Istanbul
etc
Then have the car wear look up the value for next race with either vlookup or index and match (if it is going to be a big spreadsheet I would use the latter)
|
|
|
|
|
|
#9 objavljeno 27 Ruj 2019, 19:55:50
|
Citat
|
It is possible, but it's really not a simple thing to do. You will need to write a macro in VBA, so you'd better start working on your programming skills. :)
|
|
|
|
|
|
|
#10 objavljeno 27 Ruj 2019, 20:07:20
|
Citat
|
Quote ( Brad Park @ September 17th 2019,00:14:49 ) just as well Latvia has tons of the good stuff!
That is true! Indeed :)
|
|
|
|
|
|
#11 objavljeno 27 Ruj 2019, 20:25:46
|
Citat
|
I've got the actual data regarding wear etc but it's a pain to select each track manually.
I was hoping there was a way or a script where every time the sheet is loaded, it automatically updates the current race track....
Jasper, learning VLOOKUP was a nightmare, now I have to learn more... oh what fun :D I thought I'd need to contact GPRO to see how I would allow a script to run to get the info. I'll just keep to the manual labour for now :)
|
|
|
|
|
|
#12 objavljeno 27 Ruj 2019, 21:02:12
|
Citat
|
You could make a simple macro to update the track on click of a button.
You could even have the macro change the track details, save the document and then close excel in one click.
|
|
|
|
|
|
#13 objavljeno 27 Ruj 2019, 21:45:01
|
Citat
|
Quote ( Scott MacIver @ September 27th 2019,21:02:12 ) You could make a simple macro to update the track on click of a button.
You could even have the macro change the track details, save the document and then close excel in one click.
That would be something, just no idea how :D
|
|
|
|
|
|
#14 objavljeno 27 Ruj 2019, 23:50:03
|
Citat
|
Quote ( Brad Park @ September 27th 2019,19:45:57 ) Is there anyway I can automatically pull the next few races data from GPRO to excel?
Export the CSV from Race Analysis screen.
Set-up a Master doc that has a "data" tab on it and a "working out" tab.
C&P the data into the "data" tab, and set up your formulae on the "working out" tab to look at the relevant cells on the "data" tab.
Might need a bit of tweaking, depending on your Excel level, but that is about the best way without getting into Macros and programming! :)
|
|
|
|
|
|
|
|
#15 objavljeno 21 Stu 2020, 18:14:37
|
Citat
|
Hi guys,
Sorry to ask but I'm having a mind blank here.
I forgot how to do the list function (if it's correct) where I have 2 columns, Column A has 1 to 40 and Column B has points such as 1st, 2nd and 3rd = 5 points, 4th to 9th has 4 points.
What I'm trying to do is make a spreadsheet so when someone enters in another sheet, this person finished 1st, then the spreadsheet would know to pull 5 points into the next column automatically
I hate getting older...
|
|
|
|
|
|
#16 objavljeno 21 Stu 2020, 18:17:31
|
Citat
|
sounds like you need a VLookup
So in the first part, you put the data you are looking at, then the "table" with the 1st column and 2nd column, then next you pick the column, i'd assume 2, and FALSE in the final part, should make it work
|
|
|
|
|
|
#17 objavljeno 21 Stu 2020, 18:21:44
|
Citat
|
|
|
|
|
|
#18 objavljeno 21 Stu 2020, 18:27:59
|
Citat
|
You could use a nested if statement to calculate points as well. as the first one that is true will be the answer
=If(A = 1, 6,
If(A <4, 5,
If(A <10, 4,
----continue pattern and put in another ) at the end-----
1 )))
|
|
English
Deutsch
Español
Română
Italiano
Português
Português
Français
Polski
Русский
Български
Македонски
Српски
Nederlands
Vlaams
Suomi
Lietuvių
Čeština
Magyar
Eesti keel
Slovenčina
Türkçe
Shqip
Ελληνικά
Bahasa Melayu
Dansk
Pirate
