nested "IF" formulas in enterprise fields

[anovak]

We are currently using spreadsheets to track our project portfolios and
are looking at moving that to project. We have a field called
"Magnitude" that has values such as "<= 1 wk", "<= 1 mo", etc. In
order to crunch project stats, I have a column in the Excel spreadsheet
called "Hours" that basically houses the maximum number of work hours
for the project based on the magnitude. Then, the analysis is done
based on that field.

I use a pretty complex nested "IF" formula in Excel to place the right
value in the numeric column, based on the value in the "Magnitude"
field:

=IF(R9<>"",R9,(IF(K9="<= 1 wk",40,IF(K9="<= 1 mo",160,IF(K9="<= 3
mo",480,IF(K9="<= 6 mo",960,IF(K9="<= 12 mo",1920,IF(K9="> 12
mo",2880,0))))))*(1-N9)))

Is it possible to use this type of logic in the enterprise field
formulas -- placing a certain value in the field based on the value in
another field?

Thanks,
Andy Novak
University of North Texas

 

[Dale Howard [MVP]]

Andy --

You can probably use the SWITCH function when creating the formula in an
enterprise custom field. This will eliminate the need for using nested IF
statements. Hope this helps.

 

[JackD]

Andy,

Sure you can use similar formulas. Project uses "iif" rather than "if" so
you will need to adjust the formula and there is no need for the = sign.

The custom field formulas only return data specific to an individual task or
a certain number of project fields so you can't get the formula to act on
multiple rows. Depending what you are trying to do it may still be possible
as summary tasks can be used to roll up the values.

Another option is to use some vba code to do this.

I'd caution about having a custom field formula reference the result of
another field. It can sometimes cause problems due to the calculation not
being in the proper order.