Welcome to the MacNN Forums.

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

You are here: MacNN Forums > Community > MacNN Lounge > Remedial Spreadsheetery

Remedial Spreadsheetery
Thread Tools
subego
Clinically Insane
Join Date: Jun 2001
Location: Chicago, Bang! Bang!
Status: Online
Reply With Quote
Jun 15, 2018, 11:01 AM
 
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
Administrator
Join Date: Jun 2000
Location: California
Status: Offline
Reply With Quote
Jun 15, 2018, 11:15 AM
 
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.
     
And.reg
The Mighty
Join Date: Feb 2004
Location: Well the sports issue was within arm's reach but they closed up shop and kicked me out. And I'm out of toilet paper.
Status: Offline
Reply With Quote
Jun 15, 2018, 11:21 AM
 
Let's see if I get what you're aiming for:

Sheet1:
Code:
FOO1 FOO2 FOO3 a e i b f j c g k d h l
Sheet2:
Code:
BAR1 M N O BAR2 P Q R BAR3 S T U BAR4 V W X
Sheet3:
Code:
FOO1 FOO2 FOO3 BAR1 Ma Ne Oi BAR2 Pb Qf Rj BAR3 Sc Tg Uk BAR4 Vd Wh Xl
Something like that?
This one time, at Boot Camp, I stuck a flute up my PC.
     
subego  (op)
Clinically Insane
Join Date: Jun 2001
Location: Chicago, Bang! Bang!
Status: Online
Reply With Quote
Jun 15, 2018, 02:18 PM
 
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
Posting Junkie
Join Date: Apr 2007
Location: Iowa, how long can this be? Does it really ruin the left column spacing?
Status: Offline
Reply With Quote
Jun 15, 2018, 04:58 PM
 
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
Posting Junkie
Join Date: Apr 2007
Location: Iowa, how long can this be? Does it really ruin the left column spacing?
Status: Offline
Reply With Quote
Jun 15, 2018, 05:07 PM
 
Something like this?

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

Gray cells are calculated. White cells you need to fill in.
     
subego  (op)
Clinically Insane
Join Date: Jun 2001
Location: Chicago, Bang! Bang!
Status: Online
Reply With Quote
Jun 15, 2018, 05:57 PM
 
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  (op)
Clinically Insane
Join Date: Jun 2001
Location: Chicago, Bang! Bang!
Status: Online
Reply With Quote
Jun 17, 2018, 04:10 PM
 
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  (op)
Clinically Insane
Join Date: Jun 2001
Location: Chicago, Bang! Bang!
Status: Online
Reply With Quote
Jun 18, 2018, 01:11 AM
 
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
Administrator
Join Date: Apr 2001
Location: San Antonio TX USA
Status: Offline
Reply With Quote
Jun 20, 2018, 07:03 PM
 
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.

Glenn -----OTR/L, MOT, Tx
     
   
Thread Tools
 
Forum Links
Forum Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Top
Privacy Policy
All times are GMT -4. The time now is 07:03 PM.
All contents of these forums © 1995-2017 MacNN. All rights reserved.
Branding + Design: www.gesamtbild.com
vBulletin v.3.8.8 © 2000-2017, Jelsoft Enterprises Ltd.,