Return value based on fraction of a list - VBA

[philiph]

Hi all,
I posted this in excel.functions 29th Dec, but there have been no
replies. Assuming that it IS a VBA problem, can someone in this group
help me please?

"In $U$8:Ux I have a variable length list of sizes. In $Q$4 I have a
number (1, 2, 4, 8, etc), that is used to divide $U$8:Ux into more-
or-
less equal sections/fractions. In $P$8x I need to return a set of
values (1 - Q4) that reflects the fractions in $U$8:Ux (ie they would
be the numerator of the fractions). Eg:

P U
1 23
1 16
1 1.5
1 85.6
1 23.4
1 12.8
2 45
2 23.6
2 56
2 105.1
etc

So far this formula is getting there, but I can't work out how to
finish it such that it accommodates ANY value in Q4.


=IF(SUM($U$8:U8)<SUM($U$8:$U$75)/$Q$4,1,IF(SUM($U8:U$26)<SUM($U$26:$U
$75)/$Q$4-1,2,IF(SUM($U8:U$26)<SUM($U$26:$U$75)/$Q$4-2,3,"Other
values
that I can't work out")))


Can anyone help? Preferably a formula but VBA would be good too.
Don't
worry about the SUM($U$26:$U$75) bit: I can make that take the column
length into account.
thanks,
Philip "

 

[Rick Rothstein]

I'm afraid I am not following your example. Why are the numbers in column P
a 1, 2, etc. if there is a 1 in Q4? You said the number in Q4 divides the
list in Column U into more-or-less equal sections/fractions... how does a 1
divide a group into multiple sections?

 

[JLGWhiz]

Rick, I read that two or three times before I finally decided he is using Q4
value as a divisor for the value in col P. But I could be wrong.

 

[Rick Rothstein]

I still don't get it... I do not see any pattern connections between the
values in Columns P and U and a value of 1 in Q4.

 

[philiph]

I still don't get it... I do not see any pattern connections between the
values in Columns P and U and a value of 1 in Q4.

Sorry folks, it was all clear in MY head!! :[
Let's forget about the "1" in Q4, and just go with numbers greater
than that.

Col U can have any number of values (typically 10 - 100).

Q4 is the divisor as JLGWhiz decided. This value (eg 8) is the amount
of approximately equal parts I need to divide Col U into [=< Average
(U:U) ].

Column P is divided in the same way, showing "1" for the first part,
"2" for the second part, etc (if Q4 is "2" then half Col P will be "1"
and the other half "2", ... if Q4 is 8 then 1/8 Col P will be "1",
the next 1/8 "2",and so on)

In my original example Q4 is "4", so:
U2:U7 is the first quarter of the total list [ie 1/4 so I need a "1"
in P27],
U8:U10 is the second quarter [ie 2/4 - 1/4 so I need a "2" in
P810],
U11 is the third quarter all by itself [ie 3/4 - 2/4 so I need a "3"
in P11 - sorry but I mistakenly put a "2" in my example],
U12:Ux is the fourth quarter [ie 4/4 - 3/4 so I need a "4" in P12:Ux]

Hope this helps. I'd really value a solution!

Phil

 

[Rick Rothstein]

Try this formula in P2 and copy it down...

=ROUNDUP(Q$4*COUNTA(U$2:U2)/COUNTA(U$2:U$1000),0)

Note that I assumed your maximum row with data to be 1000... change the 1000
to whatever row number will *always* be a number greater than or equal to
the maximum possible row with data in it. Make sure you don't change the
absolute/relative cell reference from what I used in my formula.

--
Rick (MVP - Excel)

I still don't get it... I do not see any pattern connections between the
values in Columns P and U and a value of 1 in Q4.

Sorry folks, it was all clear in MY head!! :[
Let's forget about the "1" in Q4, and just go with numbers greater
than that.

Col U can have any number of values (typically 10 - 100).

Q4 is the divisor as JLGWhiz decided. This value (eg 8) is the amount
of approximately equal parts I need to divide Col U into [=< Average
(U:U) ].

Column P is divided in the same way, showing "1" for the first part,
"2" for the second part, etc (if Q4 is "2" then half Col P will be "1"
and the other half "2", ... if Q4 is 8 then 1/8 Col P will be "1",
the next 1/8 "2",and so on)

In my original example Q4 is "4", so:
U2:U7 is the first quarter of the total list [ie 1/4 so I need a "1"
in P27],
U8:U10 is the second quarter [ie 2/4 - 1/4 so I need a "2" in
P810],
U11 is the third quarter all by itself [ie 3/4 - 2/4 so I need a "3"
in P11 - sorry but I mistakenly put a "2" in my example],
U12:Ux is the fourth quarter [ie 4/4 - 3/4 so I need a "4" in P12:Ux]

Hope this helps. I'd really value a solution!

Phil

 

[philiph]

Try this formula in P2 and copy it down...

=ROUNDUP(Q$4*COUNTA(U$2:U2)/COUNTA(U$2:U$1000),0)

Aye, well, it's no wonder you're a MVP Rick! Thankyou: that's the
maths I couldn't get my head around. After a play, I found that SUM
(rather than COUNTA) was more what I needed. Your maths even works if
Q4 = 1 ;].

Thanks again for helping with this, I greatly appreciate it.

Phil