[Solved] Automatically generate data from a set of values

[PotBelly]

It's been some years since I had to be able to do anything with a spreadsheet (16 years in fact) and I can still remember the basics, however, what I'm currently attempting is way beyond what my memory can recall.

I would like to automatically generate a full set of data, based on the few figures and the formula I know.

The formula is
((Cost per kilo*number of kilos purchased)+ fixed fee)/units made = cost per unit.

I have the known values for the above ((2*30) + 28)/100 = 0.88 and ((2*90) + 28)/300 = 0.69


The number of kilos purchased/units made= weight per unit e.g. 30/100 = 0.3 although I'm not sure if this helps.

The maximum that number of kilos can be is 90.

I'd like to know how to populate a table so that I can see data up to 90kg for number of kilos purchased.

Hope this makes sense!

[acknak]

You can do this (I think) with a simple table using a copied formula. Here's an example (attached).

Calc also has something called "scenarios" (I believe that's the term) for such generated tables, but I don't know how those work, or even if it would be appropriate for your use.

One thing seems odd to me with your example: the unit cost is larger for larger orders. I thought the unit cost should always go down with larger orders--economy of scale and all that.

[PotBelly]

Thanks for the attachment. The reason the cost is going up for orders is as you have it set to always /100 where as the units made increments along with the kilos purchased.

e.g.
30 Kg produces 100 units
60 kg produces 200 units
90 kg produces 300 units

[gerard24]

You can also use Data > Multiple operations :

multiple_operations.ods

(37.28 KiB) Downloaded 171 times

[PotBelly]

You can also use Data > Multiple operations :

multiple_operations.ods

Thank you so much! I've never heard of multiple operations before. That has really opened my eyes.

[acknak]

Ack! That's right, I was thinking of "multiple operations." I have to play with that sometime and if I can learn something new.

Thanks, gerard!