Sorry, the word wrap here kinda makes it hard(er) to read/understand...

Hi

Look at this: (I will leave it to you to rewrite your second IF
statement.)

Public Function GroundCover(Input1 As String, Input2 As Integer,
Input3 As Double)

' Determine if input1 = flats
If Input1 = "Flats" Then
   'Use Select Case input2 and multiply input3 by coresponding Number
   Select Case Input2
       Case Is = 4
           Output = Input3 * 0.1406
       Case Is = 6
           Output = Input3 * 0.0625
       Case Is = 8
           Output = Input3 * 0.0351
       Case Is = 9
           Output = Input3 * 0.0278
       Case Is = 10
           Output = Input3 * 0.0225
       Case Is = 12
           Output = Input3 * 0.0156
       Case Is = 18
           Output = Input3 * 0.0087
       Case Is = 24
           Output = Input3 * 0.0039
       Case Is = 30
           Output = Input3 * 0.0025
       Case Else
           Output = 0
   End Select
Else
   'Use second IF statement to check input1 and multiply input3 by
coresponding Number
   'Output=(IF(input2=4,input3*9, IF(input2=6,input3*4,
   'IF(input2=8,input3*2.25, IF(input2=9,input3*1.78,
IF(input2=10,input3*1.44,
   'IF(input2=12,input3, IF(input2=16,input3*0.56,
IF(input2=18,input3*0.45,
   'IF(input2=24,input3*0.25, IF(input2=30,input3*0.16,
   'IF(input2=36,input3*0.1111, IF(input2=48,input3*0.0625,
   'IF(input2=72,input3*0.0278,"0")))))))))))))
End If
GroundCover = Output
End Function

Regards,
Per

You could use a lookup table to get around the nesting limit.  If you set up
a table like this that contain your multiplication factors for each value of
Input 2 (lets say this table is in Sheet3!A1:C13):

4    0.1406    9
6    0.0625    4
8    0.0351    2.25
9    0.0278    1.78
10    0.0225    1.44
12    0.0156    1
16    0    0.56
18    0.0087    0.45
24    0.0039    0.25
30    0.0025    0.16
36    0    0.1111
48    0    0.0625
72    0    0.0278

and Input1 is in B27, Input2 is C27, and input3 is D27, I think this will do
what you need:

=IF(ISNUMBER(MATCH(C27,Sheet3!A1:A13,0)),D27*VLOOKUP(C27,Sheet3!A1:C13,IF(B27="Flats",2,3),0),0)

Nick VL,

   Within VBA is a statement titled Select Case.  For complicated sequences
such as you want it is easier to construct and document.  I rewrote your
function as a macro you can run from within an Excel worksheet.   It looks
bigger and more complicated because it spreads the function out over many
lines, however, that makes it much easier to write and debug.

   For an Excel worksheet a subroutine forms the basis for a macro.  I
named it the same as you named your function.  The way it works is for the
user to first select a cell where they answer is to be placed.  You then
select the run macro entry from the tools\macro menu item.  Then run macro
GroundCover.  The macro routine uses the three cells to fetch the data then
calculates the desired result and finally places that result in the cell you
select initially.

   You can select any cell on the sheet.

   If your not familiar with the Select Case statement you can go to the
Visual Basic window and use help to get a description.  The short answer is
that you enter a value or expression to be evaluated after the Select Case
statement.  Following that are a series of case descriptions in the form of
expressions.  After each case expression is one or more statements to be
executed if the condition of the case is met.

   For the routine shown, each input value is the case being selected.  The
statement after each case option defines the calculation of your result.
All other cases are then ignored and the procedure jumps to the End Select.

   Two sets of Select Case are used, one for the situation where "Flats" is
entered and a second for any other situation.  It is feasible and reasonable
to use a Select Case for the cell contents that has the word "Flats" also in
which case you could have more than two options.

   To use this in your copy of Excel, copy the entire subroutine into the
worksheet VB code window for your worksheet.  When you select the macro
window on the spreadsheet, the macro GroundCover will show up ready for use.

   Hope this longwinded answer helps.

   RDF

Public Sub GroundCover()
   'Visual Basic creates variables directly from code
   'Fill the variables with input from the worksheet
   With Worksheets("Sheet1")       ' when doing multiple actions on a
worksheet, use the With .... End Width construct
       input1 = .Cells(27, 2)         ' use .cells function to access
worksheet data
       input2 = .Cells(27, 3)
       input3 = .Cells(27, 4)
       ' Determine if input1 = flats
       If input1 = "Flats" Then

       ' use the Select Case construct instead of the nested if..  VBA code
options are much richer than EXCEL functions
           Select Case input2
               Case Is = 4
                   Ground_cover = input3 * 0.1406
               Case Is = 6
                   Ground_cover = input3 * 0.0625
               Case Is = 8
                   Ground_cover = input3 * 0.0351
               Case Is = 9
                   Ground_cover = input3 * 0.0278
               Case Is = 10
                   Ground_cover = input3 * 0.0225
               Case Is = 12
                   Ground_cover = input3 * 0.0156
               Case Is = 18
                   Ground_cover = input3 * 0.0087
               Case Is = 24
                   Ground_cover = input3 * 0.0025
               Case Is = 30
                   Ground_cover = input3 * 0.0025
               Case Else
                   Ground_cover = 0  'probably should be numeric instead of
text...  was "0"
           End Select

       Else
           'Use second IF statement to check input1 and multiply input3 by
       'coresponding Number
           Select Case input2
               Case Is = 4
                   Ground_cover = input3 * 9
               Case Is = 6
                   Ground_cover = input3 * 4
               Case Is = 8
                   Ground_cover = input3 * 2.25
               Case Is = 9
                   Ground_cover = input3 * 1.78
               Case Is = 10
                   Ground_cover = input3 * 1.44
               Case Is = 12
                   Ground_cover = input3
               Case Is = 16
                   Ground_cover = input3 * 0.56
               Case Is = 18
                   Ground_cover = input3 * 0.45
               Case Is = 24
                   Ground_cover = input3 * 0.25
               Case Is = 30
                   Ground_cover = input3 * 0.16
               Case Is = 36
                   Ground_cover = input3 * 0.1111
               Case Is = 48
                   Ground_cover = input3 * 0.0625
               Case Is = 72
                   Ground_cover = input3 * 0.0278
               Case Else
                   Ground_cover = 0  'probably should be numeric instead of
text...  was "0"
           End Select

       End If

' return result to the selected cell
   Selection.Value = Ground_cover
   End With
End Sub