Spreadsheet tracking Question

So i'm sure a number of people here tend to use spreadsheets for various aspects of their lives, from work to finance to keeping track of things for video games. Well I don't know how to ask this question properly and I know might be best served asking on an actual forum based around spreadsheets. I figured someone here would likely know anyways based on previous topics I've seen showing spreadsheet usage. So finally I'll ask!

lets say I want to calculate the value of a torch for example, and we'll say a low non-pally/sorc torch is about 5-10 Pgems, depending on overall low stats and class. If I said 5-10 and then tried to add
=sum(column id with 5-10*the column id of said torches I have) I get #VALUE!
(LibreOffice in this case and not excel, although I'm sure their formulas are similar.) <---- they were not.
I'm wondering if there's a way to make it say for example 10-20 if I had 2 of those types of torches.

Now that the breakdown is done I'll try to emulate what it looks like in my sheet.
D12 = Value of torch in Pgems in this example D12 would be [5-10]
E12 = Number of those types of torches I have so we'll say E12 would be [2]
F12 = the sum of multiplying E12 and D12 together, so F12 would be [=SUM(D12*E12)] Which spits out #VALUE!

This makes sense because trying to give it a range at least in this way might make the computer think 5 MINUS 10 which would be -5x2 for -10 but it's not sure if that's what I'm meaning to do.

My end goal is to have 5-10 change in another box to 10-20, 15-30, 20-40 for 2, 3, 4 respectively. Any Insight would be appreciated, idk if sum is the formula I want to be using or not.

Got it figured on both Excel and LibreOffice if someone else cares they are
in Excel it's =TEXTBEFORE(D12,"to")*E12&"-"&TEXTAFTER(D12,"to")*E12 changed the "-" into "to" because 5-10 would be may 10th -.-
in LibreOffice it's =LEFT(D12, SEARCH("-", D12)-1)*$E$12 & "-" & RIGHT(D12, SEARCH("-", D12))*$E$12

Now to find a way for range of numbers to combine with single set numbers.

Honestly slightly confused as to what you're trying to achieve with this in the end. Some sum of total pgem value for all combined? To what end?

I use spreadsheets for way more than I care to admit. LibreOffice as well cuz..actually tends to be superior nowadays imo once you're used to it, especially when hooking into APIs with it. Also, Linux..

As far as D2 trading goes, I set up a sheet for anni trade auto-creation for example. Torch trading would follow a similar approach.
You basically have 3 data points per torch. Attributes, resistances and class. Attributes and Resistances are probably about equal weight, each scaling from 10 to 20. Class would effectively act as a multiplier (e.g. "zon torch = 3x value while barb torch = 1x value" [arbitrary numbers]). The combination of those would then give you a total value per torch =(attributes+resistances)*classmultiplier where you can derive the classmultiplier with a simple chained if, setting it to whatever you feel right for each class. If you deem fit, add a bonus multiplier for values that are perfect.

To turn those numerical values into actual trading values, what I've done for annis (and would do for torches) is define cutoff points based on actual trade value, effectively going "30+ = 5pgems, 40+ = 10pgems, ...." [again, arbitrary numbers/values]. That's easily achieved by adding a quickly editable (for changing prices) couple columns for value and corresponding trade pricing and then checking against those (if chain) from highest to lowest. Manually defined because the price / stats tends to not move linearly (or reliably exponentially) so 15/15 isn't simply 1.5* 10/10, even more so as you close in on perfect.

If you want to take it one step further and do play on both, you can split that trade pricing by ladder and non-ladder (and/or HC/SC).

And if you really want to round it off and make your life easier for ongoing trading, you take all of that and turn it into auto-generated text for your trades here and/or your in-game trade game chat, each of which you can fine tune with for example max number of items to list and so on and so forth. If you wanna do that, just to save you the headache of figuring it out: getting a line break into your trade text in posts here is achieved by inserting a simple in your concatenate.

Ultimately, you then end up with something like this (albeit for torches) that'll make your life a whoooooole lot easier for ongoing trading (ignore the pricing...haven't farmed/traded annis in quite a while, rather outdated by now):

Spoiler

The ladder sales post for that for example then automatically looks as follows:

Spoiler

Offering

Annihilus charms.
Currently in stock:

Vex:
- 20/17/6

Ist each:
- 17/17/9
- 16/19/7
- 12/20/7
- 12/20/7
- 12/19/9

Mal each:
- 16/14/9
- 18/14/5
- 10/20/5
- 16/10/8
- 17/10/5
- 14/12/5
- 12/11/9
- 11/11/5

Yeah, i mean prices are subjective anyways, it's more the info in general I was looking for. So thank you for that.

My end goal is for a personal tracking system for 2 things, my items and combined value in PGems, not completely tho, and

Ist's value. Obv if I had 3 perfect torches that were we'll say pally/sorc/assassin, it would spit out like way more value than i'd ever need in Pgems so i'd equate it more to

Ist's, which would still be more

Ist's than i'd ever need. Either way it's mainly for that reason.

The second reason is to help myself keep a log of the items I tend to value and how much I'm willing to fork over on a constant basis. For example keeping a tracker for the value of

Gheed's Fortune because im going to want one every ladder, or torches because I don't need a 20/20 pally on my pally, a 10/10 barb will also go a long way on my pally and be way cheaper until I can afford a "proper" torch.

Admittedly, there is a third and true reason to this spreadsheet and that's just trying to use video games as motivation to learn spreadsheets, so I can hopefully translate it to more everyday usage. In the long term bettering my knowledge of tracking stuff in my daily life to help manage myself.

Your sheet looks a lot more sleek than mine, that's for sure. lol my numbers aren't really filled in because im trying to set it up more so than make use of it ATM. Also much like you values will vary.

Spoiler

This is something I wish I had gotten into during D2:LoD, because there was various price guides per ladder. Not that there isn't still, but back then the servers were split and was harder to keep track of which price guide you were looking at, at times. In D2R you have access to all regions, abolishing the need for separate price guides. That all said looking here, and various other sites that allow for trades, prices can and do vary from community to community. This in part is why I want a way for myself to have easy access to "the good stuff" if you will.

This is the main point of what I was getting at, in columns E and F I was getting #VALUE!
before doing =LEFT(C63, SEARCH(" ", C63)-1)*$D$63 & "-" & RIGHT(C63, SEARCH(" ", C63))*$D$63
Which I modified again because the 2 " " were "-" and it was making it go 10--20 for example. I also could have removed the single "-" in the middle but wanted to change it to "5 to 10" because it looked less crowded. <--- Turns out I also changed the middle "-" to " to " cause it once again looks nice this way, now to generate something to add them together. Going to also try to edit "*$D$63" to possibly just D63, in this case, so I can easily fill up a column.

Spoiler

This is just a starting point and hope to achieve something bigger than i initially expected to build. Who knows where it'll go from there.

So you're basically combining a personal inventory with a semi-personal price history? Interesting. And yeah, did not get that from the 1st post. But maybe that's just me.

From the looks of it, you may run into an issue with your search approach, at least in LibreOffice (can't remember how Excel handled it). Or you have to manually use it exclusively for those cells where you do have a range. Regardless of whether you use a space, - or "to" or whatever, find and search both error out when they don't find anything and give you a #value, messing with your further processing:
From what I gather, the above is kind of what you want. First column would be your initial value, multiplier multiplies that by whatever and turns it into final value in the last column. Columns 2 and 3 demonstrate the error described above.

You can fairly easily catch said error with iserror() and get your left value:
=LEFT(A2,IF(ISERROR(FIND("-",A2)),LEN(A2),FIND("-",A2)-1))

your right value:
=RIGHT(A2,LEN(A2)-IF(ISERROR(FIND("-",A2)),LEN(A2),FIND("-",A2)))

or ultimately the combination of both into a new string, including your multiplier:
=CONCAT(LEFT(A2,IF(ISERROR(FIND("-",A2)),LEN(A2),FIND("-",A2)-1))*F2,IF(LEN(RIGHT(A2,LEN(A2)-IF(ISERROR(FIND("-",A2)),LEN(A2),FIND("-",A2))))=0,"",CONCAT("-",RIGHT(A2,LEN(A2)-IF(ISERROR(FIND("-",A2)),LEN(A2),FIND("-",A2)))*F2)))

Adding error catches and length catches has the distinct advantage that you don't have to use some formula for the single number values and a different one for the ranges but you can just fill down the entire column and call it a day, giving you flexibility on updating as well (going from single to range and vice versa without breaking anything).

Do note that find or search give you the starting position of your target string (in this case "-"). So to drop that down to the number only on left, you simply add a -1 to the result. For right, you leave out the -1 as subtracting it from len has you covered. If you use a longer separator though (e.g. "5 to 8"), you will need to adjust accordingly. E.g. using "5 to 8" and then having find only look for "to" (no spaces) means the -1 on left needs to be -2 instead and the right statement will need a +2. Including the spaces in the find on the other hand keeps your left at -1 but ups the right to +3. So however you go about it, just remember to adjust for the shifting search/find position result and the shifting length of the separator.

Schnorki wrote: 4 months ago

=CONCAT(LEFT(A2,IF(ISERROR(FIND("-",A2)),LEN(A2),FIND("-",A2)-1))*F2,IF(LEN(RIGHT(A2,LEN(A2)-IF(ISERROR(FIND("-",A2)),LEN(A2),FIND("-",A2))))=0,"",CONCAT("-",RIGHT(A2,LEN(A2)-IF(ISERROR(FIND("-",A2)),LEN(A2),FIND("-",A2)))*F2)))

Adding error catches and length catches has the distinct advantage that you don't have to use some formula for the single number values and a different one for the ranges but you can just fill down the entire column and call it a day, giving you flexibility on updating as well (going from single to range and vice versa without breaking anything).

Hmm very interesting, I initially had asked AI how i'd go about achieving the LibreOffice one, seeing as I'm fairly new to these. So it's still a learning process to fully understand what's going on when I add/edit something.

Testing that in a new sheet is pretty much what I was looking for in terms of being able to fill easily so thank you, again, for that!

Need to keep going through items and continue adding to the list to where I want it to be then go from there to sort and find a way to add "range based" numbers together with "set" numbers. Might end up splitting the 2 if I cannot and just manually add when needed or make everything a range even if it ends up as "10-10" lol or i might attempt to get creative and spread everything across multiple sheets to hide some of the clutter.

Schnorki wrote: 4 months ago

Do note that find or search give you the starting position of your target string (in this case "-"). So to drop that down to the number only on left, you simply add a -1 to the result. For right, you leave out the -1 as subtracting it from len has you covered. If you use a longer separator though (e.g. "5 to 8"), you will need to adjust accordingly. E.g. using "5 to 8" and then having find only look for "to" (no spaces) means the -1 on left needs to be -2 instead and the right statement will need a +2. Including the spaces in the find on the other hand keeps your left at -1 but ups the right to +3. So however you go about it, just remember to adjust for the shifting search/find position result and the shifting length of the separator.

I'll have to tinker with this a bit more, cause I currently cannot get "to" to work in any form. >.> I assume it's the right side I'm getting incorrect.

Theoutrank wrote: 4 months ago

Need to keep going through items and continue adding to the list to where I want it to be then go from there to sort and find a way to add "range based" numbers together with "set" numbers.

As noted, the simplest way (in terms of usability later on) tends to be to Just treat them equally, accounting for both in your formulas and changing/interpreting/summing up as needed where needed, rather than using one way for this, another for that and potentially yet another for whatever else even though you wanna do the same thing to all of it and even though they're randomly mixed up amongst one another.

Theoutrank wrote: 4 months ago

I'll have to tinker with this a bit more, cause I currently cannot get "to" to work in any form. >.> I assume it's the right side I'm getting incorrect.

Left:
=LEFT(A2,IF(ISERROR(FIND(" to ",A2)),LEN(A2),FIND(" to ",A2)-1))

Right (note the +3 at the end now, that's what I was referring to earlier):
=RIGHT(A2,LEN(A2)-IF(ISERROR(FIND(" to ",A2)),LEN(A2),FIND(" to ",A2)+3))

Combined:
=CONCAT(LEFT(A2,IF(ISERROR(FIND(" to ",A2)),LEN(A2),FIND(" to ",A2)-1))*B2,IF(LEN(RIGHT(A2,LEN(A2)-IF(ISERROR(FIND(" to ",A2)),LEN(A2),FIND(" to ",A2)+3)))=0,"",CONCAT(" to ",RIGHT(A2,LEN(A2)-IF(ISERROR(FIND(" to ",A2)),LEN(A2),FIND(" to ",A2)+3))*B2)))

Schnorki wrote: 4 months ago

Left:
=LEFT(A2,IF(ISERROR(FIND(" to ",A2)),LEN(A2),FIND(" to ",A2)-1))

Right (note the +3 at the end now, that's what I was referring to earlier):
=RIGHT(A2,LEN(A2)-IF(ISERROR(FIND(" to ",A2)),LEN(A2),FIND(" to ",A2)+3))

Combined:
=CONCAT(LEFT(A2,IF(ISERROR(FIND(" to ",A2)),LEN(A2),FIND(" to ",A2)-1))*B2,IF(LEN(RIGHT(A2,LEN(A2)-IF(ISERROR(FIND(" to ",A2)),LEN(A2),FIND(" to ",A2)+3)))=0,"",CONCAT(" to ",RIGHT(A2,LEN(A2)-IF(ISERROR(FIND(" to ",A2)),LEN(A2),FIND(" to ",A2)+3))*B2)))

Ahh okay, I had changed all to " to " and added the +3 near the end assuming since there was only 1, -1, that there would only be 1, +3. Turns out I needed 2, which was going to be the next test of adding numbers and assuming. lol it's a lot more enjoyable to tinker and learn spreadsheets than I thought possible.

Well, teeeeechnically you only need one.
More accurately, you should only use one. Which may even be a -3 instead. Just to add some confusion.

The above combination, for the sake of simplicity, basically uses the 'right' value twice, once to check if there is a second number (i.e. a range) and once to then write it out. Theoretically, you don't need the full value for the check. You also don't need to repeat the iserror check more than once. If you make it an overarching one, you can catch all of it in one go and spell out your (then simplified) formula accordingly. Since that would be the more performant approach, it is worth pointing out and wouldn't be a bad thing to get used to as you're still teaching yourself.

For this use case though, performance doesn't matter (you'll never see a difference with the amount of data/lines you'll realistically be using) so I left it as "using 'right' twice" for the sake of simplicity and re- and deconstructability (is that even a word? if not, it should be..).

More optimized, the combined column would be as follows:
=IF(ISERROR(FIND(" to ",A2)),A2*B2,CONCAT(LEFT(A2,FIND(" to ",A2)-1)*B2," to ",RIGHT(A2,LEN(A2)-FIND(" to ",A2)-3)*B2))

Schnorki wrote: 4 months ago

Well, teeeeechnically you only need one.
More accurately, you should only use one. Which may even be a -3 instead. Just to add some confusion.

Interesting with just one though it kept spitting out #VALUE!, mind you this was the initial one w/o any 3's added. lol

Schnorki wrote: 4 months ago

The above combination, for the sake of simplicity, basically uses the 'right' value twice, once to check if there is a second number (i.e. a range) and once to then write it out. Theoretically, you don't need the full value for the check. You also don't need to repeat the iserror check more than once. If you make it an overarching one, you can catch all of it in one go and spell out your (then simplified) formula accordingly. Since that would be the more performant approach, it is worth pointing out and wouldn't be a bad thing to get used to as you're still teaching yourself.

Hmmm I might write down and test a bit of sheets instead of constantly repairing my current one, then once I understand what I need to go from there. Maybe write down items, rough values, how I want to sort, how/what i want to compare to (

Perfect Gems / Ists / HR) then test formulas against it. Also should read on LibreOffice the various outputs, mind you I'm sure there's more than I'd expect, just one I looked into had a bunch and it never even mentioned concat. >.>

Schnorki wrote: 4 months ago

More optimized, the combined column would be as follows:
=IF(ISERROR(FIND(" to ",A2)),A2*B2,CONCAT(LEFT(A2,FIND(" to ",A2)-1)*B2," to ",RIGHT(A2,LEN(A2)-FIND(" to ",A2)-3)*B2))

It's crazy to see how you've evolved this over a few posts, condensing it and slightly reconstructing to do, to me, the same thing over. It's pretty fascinating to me. lol

Schnorki wrote: 4 months ago

image.png

I cannot quote this image but it's cool to see you outlined the 2 types of sorting I was looking at and will hope there's a way to conjoin them w/o needing 2 separate total values.

For example
A2:2 ------------------B2:2 -------------C2:4
A3:9 ------------------B3:3 -------------C3:27
A4:2 ------------------B4:5 -------------C4:10
A5:7 ------------------B5:10 -----------C5:70

A9:7 to 10 ----------B9:4 -------------C9: 28 to 40
A10:5 to 8 ----------B10:5 -----------C10: 25 to 40
A11:1 to 3 ----------B11:10 ---------C11: 10 to 30
A12:10 to 15 -------B12:3 ----------C12: 30 to 45

A15:TOTAL ---------B15: ------------C15: 204 to 266

Combining both ranged and single. 111 for singles added to both left and right numbers. So 93 to 155 from C9-C12 + the 111 from C2-C5

Theoutrank wrote: 4 months ago

For example
A2:2 ------------------B2:2 -------------C2:4
A3:9 ------------------B3:3 -------------C3:27
A4:2 ------------------B4:5 -------------C4:10
A5:7 ------------------B5:10 -----------C5:70

A9:7 to 10 ----------B9:4 -------------C9: 28 to 40
A10:5 to 8 ----------B10:5 -----------C10: 25 to 40
A11:1 to 3 ----------B11:10 ---------C11: 10 to 30
A12:10 to 15 -------B12:3 ----------C12: 30 to 45

A15:TOTAL ---------B15: ------------C15: 204 to 266

Combining both ranged and single. 111 for singles added to both left and right numbers. So 93 to 155 from C9-C12 + the 111 from C2-C5

So...
?

Easiest way of achieving that imo is via helper columns (that you can hide once done).
Specifically, the above actually looks like this without hidden columns:
For the first set,
Single:
=IF(ISERROR(FIND(" to ",A2)),A2,0)+0

Range.Left:
=IF(ISERROR(FIND(" to ",A2)),0,LEFT(A2,FIND(" to ",A2)))+0

Range.Right:
=IF(ISERROR(FIND(" to ",A2)),0,RIGHT(A2,LEN(A2)-FIND(" to ",A2)-3))+0

For the ones following B, you are simply using
=B2*$E2
Where B2 is Single for the multiplied Single and $E is your labeled column B (so the multiplier). The $ just allows you to enter that once for the first Single multiplied value and then fill not only down but also to the right.

The sum for A is
=IF(SUM(D:D)=0,SUM(B:B),CONCAT(SUM(B:B)+SUM(C:C)," to ",SUM(B:B)+SUM(D:D)))

The sum for C is
=IF(SUM(H:H)=0,SUM(F:F),CONCAT(SUM(F:F)+SUM(G:G)," to ",SUM(F:F)+SUM(H:H)))

And C itself for the values is
=IF(H2=0,G2,CONCAT(G2," to ", H2))

For the sums, checking for the right sum to be 0 should be sufficient as "x to 0" shouldn't be a realistic/valid entry (meaning you never end up with a sum 0 for right if you have any 1 or more range values).

There's a bit of trickery going on here that may otherwise take you forever to figure out.
First and foremost, I'm splitting the single values and range left/right from each other, rather than combining single and range.left as was the case in previous posts. The reason for that is simply to make the totaling math easier as you go from single_sum+left_sum to single_sum+right_sum. If you combined single and left you'd have to break them apart within the sum/total formula so you don't count the non-single left twice in the total for right. Way more annoying than just using separate helpers.

Also, quite importantly, you may notice a rather random seeming +0 at the end of each of the first 3 helper columns. That's because single-cell math converts string to numbers but sum() actually doesn't. So in order to get your sum() to work correctly, you have to force each of your single/left/right cells to spit out a number-formatted value, rather than a text representing a number. Setting the cell format alone does not actually suffice for that as the left/right still produce a text result. Taking your final result and adding 0 to it however converts any such text into a number (that doesn't change because you're just adding 0) and then has the cell count as a number value. As a result, you can then use all of them in sum().

Personally, after decades of spreadsheeting just about anything life throws at ya (and semi-automating entire previous jobs with it), I have found hidden helper columns to be one of the best things you can use for many, many things. Not only do they make the building process easier (as you go step by step) but they also allow you to validate each step individually and equally trouble-shoot any possible issues individually later on. In some cases they may cost you some performance but if built well, the difference won't be too big regardless and again in this use case really won't matter at all.

Schnorki wrote: 4 months ago

Personally, after decades of spreadsheeting just about anything life throws at ya (and semi-automating entire previous jobs with it), I have found hidden helper columns to be one of the best things you can use for many, many things. Not only do they make the building process easier (as you go step by step) but they also allow you to validate each step individually and equally trouble-shoot any possible issues individually later on. In some cases they may cost you some performance but if built well, the difference won't be too big regardless and again in this use case really won't matter at all.

Hey, sorry to ghost. Been busy lol I appreciate your help overall and am going to restructure my sheet in the next coming weeks. Going to read into the helper columns and see what's up.