Data Validation Formula To Detect If Number Not Nearest 0

[muhammad susanto]

hi all...

i'm looking for data weighted with smallest number-nearest number 0 (absolut number)
Excel 2007 32 bit

	C​	D​	E​	F​	G​
3​		a	b	c	total
4​	data	-2​	4​	1 ​
5​	weighted	20%​	30%​	50% ​	100%​

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

weighted number is given based on the smallest number (nearest number 0)
i need data validation formula to detect if weighted is not true...
the total weighted must 100%
from data 1 (nearest 0), the weighted given must greates number from 3 number -2,4,1

any assistance much appreciated..
m.susanto

 

[oldbrewer]

		2	4	7	4	1			1	2	4
									50%	30%	20%
	data	-2	4	7	4	1
	weighted	30%	20%	#N/A	#N/A	50%	100%
	assumed problem statement							col J
	find the 3 numbers closest to zero and assign them 50%,30%,20%
		formula in C4 (30%)
		=OFFSET($I$1,1,MATCH(C1,$J$1:$L$1,0))

<colgroup><col><col><col span="12"></colgroup><tbody>
</tbody>

 

[muhammad susanto]

		2	4	7	4	1			1	2	4
									50%	30%	20%
	data	-2	4	7	4	1
	weighted	30%	20%	#N/A	#N/A	50%	100%
	assumed problem statement							col J
	find the 3 numbers closest to zero and assign them 50%,30%,20%
		formula in C4 (30%)
		=OFFSET($I$1,1,MATCH(C1,$J$1:$L$1,0))

<tbody>
</tbody>

hi...

how to it work...

 

[muhammad susanto]

Rule :

data weighted is given, with not consistent number..
i mean can like example :

20%,10%,70%
50%,20%,30%
40%,40%,20%

but the total must be = 100%

i want to know in Data Validation, how to give weighted number (input manual) not mistake type..
example :

if have 3 data number
7,-5,1---weighted biggest number is given to number 1.
-4,8,10 -- weighted biggest number is given to number 4

if i entry biggest number is wrong, data validation showing/alert information....

 

[muhammad susanto]

i hope this sample make clear...

Excel 2007
	C	D	E	F	G
16	data a	data b	data c
17	5	-2	3
18	25%	45%	30%	correct
19	10%	40%	50%	not correct	showing error alert in Data Validation
20	60%	30%	10%	not correct	showing error alert in Data Validation
Sheet1

 

[oldbrewer]

how do you determine -2 is 45% and not 40% ?

 

[muhammad susanto]

how do you determine -2 is 45% and not 40% ?

cause, -2 is nearest zero, and 45% or 40 or etc...is given number not have the rule. The rule, total 3 data must be 100% (25%+45%+30%) etc.....
i just want to know & getting information if i wrong placing in cell, like post #5 above then showing error alert in Data Validation

 

[muhammad susanto]

bump..

 

[oldbrewer]

finding numbers closest to zero is easy, but how do you decide what percentages to apply ?

example - the 3 nearest numbers to zero from 2,7,8,9,-9,-8,7,-1,3,10,12,6 are -1,2,3

tell us how you assign percentages to them.