if statement with ranges

This is my question/issue – I am trying to create an estimating tool for software projects.  Based on 3 or more project types (A, B, C) the three major roles (business analyst, developer and quality assurance) have different level of efforts (time they will spend on the project).

I created the following formula to calculate/estimate each effort based on project type (A, B, C) estimates =IF($D7="A",'Project Types'!$B$4*$T7,IF($D7="B",'Project Types'!$B$9*$T7,IF($D7="C",'Project Types'!$B$14*$T7,"TBD"))), for this example let say this formula returns “20” in the cell.

What I would like to do is calculate a range, say plus or minus 30%, thereby giving me “14 – 26” in the cell. 

I know I could probably add various cells and do a bunch of calculations and then concatenate the results – but what I would like to do is handle this through 1 formula.  Any suggestions?

Enter your posted formula in A1 and change "TBD" to 0. (zero)
Use the following formula to get the desired result...

=IF(A1>0,0.7*A1 & " - " & 1.3*A1,"TBD")
'--
Jim Cone
Portland Oregon USA
http://www.mediafire.com/PrimitiveSoftware

that works GREAT - thanks for you help

One more question, how do i "code" this so it only shows whole numbers 26 not 26.7?

Your post did not appear until today(Aug 28, 2010)...

Try:  =IF(A1>0,TRUNC(0.7*A1) & " - " & TRUNC(1.3*A1),"TBD")

You can also use Int or Round instead of Trunc.  See Excel help.
'--
Jim Cone
Portland, Oregon USA
Special Sort add-in (review of)

Jim Cone:

Your post did not appear until today(Aug 28, 2010)...

It was awaiting moderation Jim, and I didn't spot it until late.