Dynamic (not conditional) formatting

[ansky]

I'm using the following formula... (Excel 2003)
=SUMIF(DataResource,Role,ResourceHours)/IF(DisplayAs="FTEs",40,1)

I would like the number displayed to show one decimal if DisplayAs="FTE", but show integers if not...

I've tried conditional formatting but that doesn't allow changing the number formatting.

Anybody have an idea?

Thanks!

[NBVC]

Not sure if this is what you mean?

Please Login or Register  to view this content.

[Andy Pope]

Use formula to format result

=IF(DisplayAs="FTEs",TEXT(SUMIF(DataResource,Role,ResourceHours)/40,"0.0"),TEXT(SUMIF(DataResource,Role,ResourceHours),"0"))

or

=IF(DisplayAs="FTEs",ROUND(SUMIF(DataResource,Role,ResourceHours)/40,1),ROUND(SUMIF(DataResource,Role,ResourceHours),0))

[NBVC]

I used TRUNC() instead of ROUND() because I am not sure you wanted to round to 1 decimal or just display that first decimal without rounding..... the TRUNC() just truncates at the decimal point you request.