[subego]

Let’s say I’ve got a bunch of FOOs, which I put in my columns, and a bunch of BARs, which I put in my rows.

At any given intersection of a FOO and a BAR, I have multiple pieces of data to compute.

Is the only way to do this something along the lines of picking one (BAR in this example), and setting it up like...

BAR1data1
BAR1data2
BAR2data1
BAR2data2

Or

BAR1data1
BAR2data1
BAR1data2
BAR2data2

Does this question even make a lick of sense? In my mind I want a sheet of FOOs and a sheet of BARs, and then a third sheet which connects the two, but that doesn’t seem to be how spreadsheets like to work.

[reader50]

Not specific enough. What are you trying to do, task-wise? You can make it fictional. Dinosaurs keeping track of their financing for mattress payments.

[...deleted...]

[subego]

I may have worked this out while putting it into words, but here’s the non-abstract version.

I’ve got SONGS, which supposedly make money but not really, and then PEOPLE, who need to get paid their fraction of a cent.

That’s the initial calculation I need to make. What percentage does each person get for each song.

Okay... even I can figure this part out.

From there, I can calculate what each person earned per song, and then based on what they’ve been paid so far, find out how much they’re still owed.

If I give each SONG a single entry, it seems I’m stuck making each PERSON something like...

BobPercent
BobEarned
BobPaid
BobOwed

[Laminar]

Does each person earn the same percentage per song? Do you want to know how much each person is owed on a per-song basis or just a total amount owed?

[Laminar]

Something like this?

https://docs.google.com/spreadsheets...it?usp=sharing

Gray cells are calculated. White cells you need to fill in.

[subego]

The per-song doesn’t matter in terms of it being useful data for real life, but I can’t calculate the total without calculating the per-song.

For the percentages, it’s all over the place, but I realized unless my spreadsheet is going to accommodate people dying, those percentages are fixed, so I need to resist my programmer impulse to have this be a variable calculated on the fly, and instead have these be a set of constants for each PERSON

I think if I do this, I can have each PERSON be their own sheet, which is what I want. All I have to do is pipe the data from a sheet with the total earnings for each song to each PERSON sheet.

This will make adding a PERSON easy. I just copy a sheet and enter in the correct percentages. I won’t have to rewire it to work. That’s one of the things I’m worried about. I need to be able to figure out how to add a new person even if I haven’t done it in a year. I can figure out I need to fill out percentages.

Adding a new SONG is kind of annoying, because I have to update every PERSON sheet individually, but that doesn’t seem difficult for me to figure out a year from now, just tedious.

[subego]

Well, the opportunity to talk this out was immensely helpful, so even if everything I’ve said makes no sense, I’ve figured out how I want to design this.

Many thanks!

[subego]

Also, in case my thread in Applications was missed, anyone have an opinion on whether the MacOS sandbox is enough of a safety measure to risk turning off the macro warning on Excel?

[ghporter]

I had a supervisor once who got a lot of mileage out of the phrase “say it out loud.” It helps structure your logic, it helps phrase your language, and sometimes it helps you avoid asking the stupidest question in the world of the worst person to ask a stupid question. But I found it great in both programming (meta coding, usually) and writing English text (presentations, exportable training, etc.).

Glad we could be a sounding board for your brainstorm.