Excel advice
Images are sometimes not shown due to bandwidth/network limitations. Refreshing the page usually helps.
You are currently reading a thread in /adv/ - Advice
You are currently reading a thread in /adv/ - Advice
This is probably the last thing you were expecting on this board, but I need some advice writing a formula in Excel. I have a number list that incrementally increases and I want to do the following to it:
Separate out each increment of 100 and divide it by n+1, where n is the 100s place digit. And then add the results
i.e. any number up to 100 will be divided by 2, anything between 101 and 200 will be divided by 3, any number between 201 and 300 will be divided by 4, etc. and then add the results together.
I think I could piece it together if there was a way to identify how large a number is and then cut it into pieces. I could do this all by hand of course, but this is over 108 numbers so I'd rather not do that
>>17150872
1. Go to skulpt.org. You will see an online Python interpreter.
2. Paste the following code and run, replacing the numbers in list with your actual list of numbers.
list = [123, 456, 5789]
a = 0
for i in list:
a += i/(float((i/100)%10)+1)
print(a)
>>17150956
er, the line a += i/(float((i/100)%10)+1) needs a tab before it. then it should work.
>>17150956
>>17150961
So there is no way to do this in Excel? If possible I'd like to write the formula into a cell, that way I can copy it down the entire list.
>>17151018
Also this is just shifting the work from me hand doing the math to hand entering each value in the list. The whole point of doing this with a program is to remove having to do something over 100 times.
>>17151028
too lazy to test the below, but it should work
=somecell/(MOD(QUOTIENT(somecell,100),10)+1)
copy the formula for each cell and then use SUM(), hopefully you know excel basics so i don't have to explain how to do that
>>17151051
here's a few examples.
input: 98, output: =[cell]/2
input: 116. output:=(100/2)+(([cell]-100)/3)
(I know that this is a terrible way of doing it, but it works for the next 100).
input:204, output: =(100/2)+(200/3)+((R31-200)/4)
(once again I'm doing it the long way and this is ineligant as fuck)
>>17151062
oh my god that's so stable that I could kiss you.
>>17151140
Don't quite understand the syntax myself, but would I need to alter it for numbers below 100 and numbers above 1000?
>>17151150
I ask because for a value of 74, the correct response should be 37 (#<=100 gets divided by 2). Likewise once the numbers go over 1000, it jumps to 500 something.
>>17151159
Whoops. What you're probably looking for is the formula, I posted, but with a +2 instead of +1. Tell me if you find any cases that don't work otherwise.
>>17151227
it works great until I hit any number above 1000. the numbers go something like:
Input: 996, output: 90.54545
then
Input: 1010, Output: 505
>>17151227
Found a case. Every clean multiple of 100 will return an incorrect answer. Sorry.
>>17151257
1010/(0+2)=505
If you wanted to divide by 10, remove the "MOD(" and the ",10)" bit
>>17151279
here's what the formula should do (I did it by hand to compare the results)
Input: 1010
My formula: =(100/2)+(100/3)+(100/4)+(100/5)+(100/6)+(100/7)+(100/8)+(100/9)+(100/10)+(100/11)+((R95-1000)/12)
Output of my formula: 202.8211
Output of your formula: 505
