Really long Basic formula

[bromy2004]

Hi Everyone,

I have a formula that does exactly what i want, but my friend cant use it on his computer (he doesn't have Excel 2007)

I'm trying to create a Custom Function/ UDF to do this in excel VBA but ive hit a bit of a road bump.

=IF((OR(E13=0,ISBLANK(E13))),IF((OR(H13=0,ISBLANK(H13))),IF((OR(K13=0,ISBLANK(K13))),IF((OR(N13=0,ISBLANK(N13))),IF((OR(Q13=0,ISBLANK(Q13))),IF((OR(T13=0,ISBLANK(T13))),IF((OR(W13=0,ISBLANK(W13))),IF((OR(Z13=0,ISBLANK(Z13))),IF((OR(AC13=0,ISBLANK(AC13))),IF((OR(AF13=0,ISBLANK(AF13))),IF((OR(AI13=0,ISBLANK(AI13))),IF((OR(AL13=0,ISBLANK(AL13))),"99","12"),"11"),"10"),"09"),"08"),"07"),"06"),"05"),"04"),"03"),"02"),"01")

my VBA code at the moment

Function LatestSales(Sales_Range)
Dim Sales_Range As Range
Dim Range_Value As Integer
Dim n2 As Integer
Dim July As Integer
Dim August As Integer
Dim September As Integer
Dim October As Integer
Dim November As Integer
Dim December As Integer
Dim January As Integer
Dim Febuary As Integer
Dim March As Integer
Dim April As Integer
Dim May As Integer
Dim June As Integer

Range_Value = 0
n2 = 3
For Sales_Range = 1 To Count.Cells.Sales_Range Step 1
Range_Value = Range_Value + 1
If Int((Range_Value + 2) / n2) = (Range_Value + 2) / n2 Then
MsgBox ("Whole number")
Else
MsgBox ("Not a whole number")
End If

End Function

My formula checks the first cell in a range (always a Row) and every third one thereafter. and simply returns the associated number.

Any help and comments would be great,

thanks in advance
-Bromy

 

[sanrv1f]

it is always hard to follow a formula without seeing the data on which it is working,

could you post some sample data and the expected results?

 

[bromy2004]

it is always hard to follow a formula without seeing the data on which it is working,

could you post some sample data and the expected results?

i cant quite figure out how to attach a spreadsheet so ill try to explain.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
9	*	CUSTOMERS			MONTHS
10	*				July			August			September
11	*	CODE	NAME	QUICKCORP CATEGORY	Value	Margin	Mgn %	Value	Margin	Mgn %	Value	Margin	Mgn %
12	*	100001	*	*	*	*	*	*	*	*	*	*	*
13	*	100002	*	*	*	*	*	*	*	*	*	*	*
14	*	100003	*	*	*	*	*	*	*	*	248.10	65.55	26.4%
15	*	100004	*	*	*	*	*	104.00	38.98	37.5%	*	*	*
Template

So my Formula goes into A13 and gets copied down.
the Months at the top go all the way to June (Australian Financial Year)
its checking all months for the Value in "Value" if its Blank or value of 0.00 go to the next, otherwise return value.

Sales in July - 01
August - 02
September - 03
October - 04
and so on...

So Rows 12/13 return 99 (No Data at all)
but 14 returns 03
and 15 returns 02

Does this help?

 

[sanrv1f]

try

E12

=MIN(IF($E$11:$AL$11="Value",IF($E12:$AL12<>0,ROUNDUP((COLUMN($E12:$AL12)-COLUMN($E12)+1)/3,0),99)))

or

=MIN(IF(MOD(COLUMN($E12:$AL12)-COLUMN($E12)+1,3)=1,IF($E12:$AL12<>0,ROUNDUP((COLUMN($E12:$AL12)-COLUMN($E12)+1)/3,0),99)))

both formuals confirmed with Ctrl+Shift+Enter and copied down

 

[bromy2004]

thank you, they both work perfectly,

could you please explain how they work?

-Bromy

 

[sanrv1f]

=MIN(IF($E$11:$AL$11="Value",IF($E12:$AL12<>0,ROUNDUP((COLUMN($E12:$AL12)-COLUMN($E12)+1)/3,0),99)))



first IF checks for column headings containing "Value", the result would be an array of TRUEs&FALSEs

{TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,FALSE}


those columns that doesn't have "Value" would return FALSE and they would be excluded from the MIN function,

the second IF checks for the content in the cells E:AL and if they are non zero then proceed further, otherwise return 99 to the MIN function

the range E:AL is split into groups of 3 columns,

the

ROUNDUP((COLUMN($E12:$AL12)-COLUMN($E12)+1)/3,0)

part would return the group each column belongs to in the range,

{1,1,1,2,2,2,3,3,3,4,4,4,5,5,5,6,6,6,7,7,7,8,8,8,9,9,9,10,10,10,11,11,11,12}


now the columns with zero value would get their group replaced with 99 and columns without title "Value" would get their group replaced with FALSE

{99,FALSE,FALSE,99,FALSE,FALSE,3,FALSE,FALSE,99,FALSE,FALSE,99,FALSE,FALSE,99,FALSE,FALSE,99,FALSE,FALSE,99,FALSE,FALSE,99,FALSE,FALSE,99,FALSE,FALSE,99,FALSE,FALSE,FALSE}

and the MIN function chooses the lowest vaue from the list

 

[bromy2004]

Thank you,
You've taught me alot.

-Bromy