complex functions with scenarios and relative addresses

[NewAtFunctionsMike]

I am trying to write a function that will help me to budget for multiple
scenarios. Function: SalaryAmt,

Sample Datasheet:

A B C D E F G H I J
1 Month1 Month2 Month3 Month4
2 UseSalary 1 1 2 3
3 Annual Annual Annual 1/1/2005 2/1/2005 3/1/2005 4/1/2005
4 Name StartDate EndDate Salary1 Salary2 Salary3
5
6 Tom 1/1/2005 2/28/2005 10000 23000 43000 =fn =fn =fn =fn
7 Dick 3/1/2005 12000 14000 16000 =fn =fn =fn =fn
8 Harry 3/1/2005 14000 16000 18000 =fn =fn =fn =fn
9 Sally 4/1/2005 35000 38000 40000 =fn =fn =fn =fn


The scenarios, the numbers on row2 following UseSalary, are calculated on
another sheet and are based on net revenues for that month. One variable as
to how much a person can be paid is the net revenue situation.

If I insert the SalaryAmt function in any cell in within G6:J9 (the range
will change as I add months and people), the following is my attempt at the
proper logic:

‘ I am assuming that the active cell is already formatted for currency, no
$, no decimals.
‘ I think the function needs to know where it is

NowIamAt_r = ActiveCell row
NowIamAt_c = ActiveCell column

returnvalue= AS currency
loopcontrol=.T.

DOWHILE loopcontrol

‘ First test, should the person be paid this month? They should not be paid
if the EndDate applies

IF Month1Date [NowIamAt_c,$3] is > EndDate [$C,NowIamAt_r]

returnvalue = $ 0
loopcontrol=.F.
EXIT

ENDIF

‘ If there is no EndDate, then need to see if the start date is same or
earlier than this month’s date

IF ThisMonthDate [NowIamAt_c,$3] is >= StartDate [$B,NowIamAt_r]
‘ It this test is passed, need to fill in value for the governing scenario
Select.case
Case [NowIamAt_c$2] = “1â€
returnvalue = ([$D, NowIamAt_r]/12)
Case [NowIamAt_c$2] = “2â€
returnvalue = ([$E, NowIamAt_r]/12)
Case [NowIamAt_c$2] = “3â€
returnvalue = ([$F, NowIamAt_r]/12)
Case [NowIamAt_c$2] < “1â€
Change variable type to text
returnvalue = “error<1â€
Case [NowIamAt_c$2] > “3â€
Change variable type to text
returnvalue = “error>3â€
ENDCASE

ELSE

‘If test is not passed, returnvalue is zero

returnvalue = 0

ENDIF

Loopcontorl = .F.

ENDDO

 

[GaryDK]

Hi Mike,

I think your best bet is to handle this with a formula. It certainly
looks like it lends itself to a formula solution. If I understand your
layout correctly, you should be able to drop the following formula into
cell G6, and drag copies through your monthly salary range -

=IF($B6>$C6,0,IF(G$3>$B6,INDEX($D6:$F6,1,G$2)/12,0))

Because the formula uses the index function, you should round your
UseSalary cells to be integers. So, if the formula in the Month1 cell
is -

=OtherSheet3!D12

you should change it to -

=ROUND(OtherSheet3!D12,0)

I hope this helps,

Gary

 

[NewAtFunctionsMike]

Gary, many thanks for your suggestion. The formula approach works (after
some adjustments for the real sheet and after adding an AND function). The
net result, from the real sheet, is:
=IF(AND($C5>G$4,$B5<=G$4),INDEX($D5:$F5,1,G$3)/12,0). The AND is necessary
to test for both conditions precedent, "has started" and "not terminated".
In order to make this work, I had to input an enddate for everyone, using an
enddate far into the future.
BTW, the output did not conform to the existing cell formatting, and there
does not appear to be any Excel built-in formating functions. Minor issue
that is solvable with selecting cells and using a macro.
Your help is much appreciated.
Michael

 

[GaryDK]

I'm glad that it worked for you. Regarding the formatting issue, what
do you mean by "the output did not conform to the existing cell
formatting"? I don't know how a numeric value can override your
currency formatting, unless you have conditional formatting that is
interfering with what you want.

Gary