Table that calculates curtain folds based on 4 specific criteria

[JBeaucaire]

The attached file shows a quandry I am unable to fathom. It's a chart for calculating the points of folds across a curtain.

There are four fields:
LENGTH - the length of the whole curtain
FOLDS - number of folds in curtain (always has a half-fold at the end)
ALLOWANCE - amount to be deducted from LENGTH before any other calcs occur
DROP - the amount of fabric used in each "fold".

A "drop" of 25mm actually uses 50mm of fabric because it drops 25 and rises 25.

The sample sheet shows many examples of "results" on the left in the boxes.

The median is spotted, then folds are measured outward from there, up and down.

THE GOAL - the chart in tan on right must be able to calc the folds without the helper cells the samples on the left are utilizing. The YELLOW cells in the samples are what we want to appear in the red box based on the values entered there. The red box needs to be standalone, no lookups or references outside the box.

Thus I am stumped.

There is a second sheet where you can see some of my flounderings with the MEDIAN formula...

(Cross-posted here)

[WHER]

Let's start small: the following formula in Sheet1 cell P6, dragged down, will give the the list of folds according to the number in cell O7 (i think)

=IF(ROWS($A$1:A1)<=INT($O$7),ROWS($A$1:A1),IF(ROWS($A$1:A1)=INT($O$7)+1,"",IF(ROWS($A$1:A1)=INT($O$7)+2,$O$7,"")))

[JBeaucaire]

Sheet has been updated with WHER's formula and a couple of note corrections at the top.

http://www.excelforum.com/attachment...ia-book21.xlsx

[WHER]

A question: on sheet 1, the numbers in column G (-7, -9, -10), how are they calculated?

[stephen1000]

Hi Wher

we have touched base in the past, over this problem

JB has become a how to say (I hope he wont mind) a valued friend.....over the past year.

he has helped me more than words can express, but He's a bit stuck

the -7 -8 -9 is reached by CEILING((SUM($F$10:$F$14)-$c$10)/$B$11,1)

Rounded up



For example:- 1655mm drop
i 1655mm - 155mm = 1500mm workable fabric
25mm gradiant, 5 folds
1500
5.5 = 272 mm rounded down to the nearest mm

ii Adjust to form gradiant by adding or subtracting 2 x gradiant (50 mm)
the new total is now 1546 mm but there is only 1500 mm of workable fabric so there is a deficit of 46mm.
46
5.5 spaces = 8.36 mm

Therefore adjust each space down by 9mm as in step iii.
The new Total will be 1497 mm and if you plan form the bottom it only increases the final top space by 3mm.



Cheers for your Help

JB you ROCK Dude

[JBeaucaire]

Stephen, thanks for the clarity. My eyes still cross a little on this sheet.

When you give examples, try to use the data in the sample sheets so the helpers can directly see what you're exemplifying.

[WHER]

This is how far i've got, see attachment.
First of all, i used (hidden) helper columns R, S, T, i really can't imagine doing all these calculations in one go.
For 3.5, 4.5, 6.5 and 9.5 folds my end results in column Q match your example results (yellow) in column I. This coïncides with the instances where my calculations for the value "-7, -9, -10" (darker blues) for column G coïncide with yours (light blue ), when the results for column G differ (for folds 5.5, 7.5, 8.5), my end results in Q6 down differ slightly from your yellow example results.

[JBeaucaire]

Very nice. I did notice that some of the original "examples" seemed to round down and some round up, no particular rhyme nor reason to which. This appears to be consistent.

Stephen?

[stephen1000]

Hi JB / WHER

JB, probably should all round up acording to the calculations on paper I have;

I think beacause we are talking mm's not sure it really matters .....

so lets go for round up

thanks again peeps for all your input and support.

steve

[JBeaucaire]

Then what do you think? This appears to work for the samples given...

[stephen1000]

Hi Guys,

I have quickly looked at it, Looks very good; i have also tried changing the length to random values ie 1230, 1450, 1900, 1420. seems to work.

cheers

will look in more detail tonight

thanks guys

[stephen1000]

Hi Guys

JB, WHER thanks for all your input looks we cracked it

thanks again

steve