MrExcel.com - Your One Stop for Excel Tips & Solutions

Throwing in the towel

Posted by Bob on July 28, 2001 2:43 PM

Calling for help after surfing the archives and help
files all day. I'm trying to make a generic estimate
worksheet so all I have to do is enter the geometric
shape (by entering c,d,h etc in col c)along with a
couple of dimensions and have Excel extend all the
various PI() calcs for me. Problem(s)encountered:
1) 1st attempt with tons of IF() gave me
the "too many" error message, so I; 2)Tried to use
Vlookup() but, as all PI() calcs in the formula key
off Col D (diameter) I could not figure out how to
reference it so; 3)Went back to IF() and got it to
accept my scaled down formula, but; alas, now I can't
get it to calc unless I enter 1's in all blank
height/width/length cells.
I put up a screencap of the ws & formula here:
http://home.earthlink.net/~bobferg/misc/estimate.html

Posted by Aladin Akyurek on July 28, 2001 4:00 PM

Bob,

Are

2467.4011
10

the expected results for the entries on the screenscrap?

If not, care to provide them?

Aladin

============

Posted by Bob on July 28, 2001 4:20 PM

Thanx for the reply. In this example the results would be 785.40 & 10, the first number calced as the formula for a circle as PI times radius squared, which I tried to acheive in Excel as PI()*(d2/2)^2 with cell d2 being diameter so radius achieved by diameter halved. I actually get results in "Square Feet"/Col H if I type numeral 1 in the blank cells.


Posted by Aladin Akyurek on July 28, 2001 4:46 PM

Bob,

You need test it more:

=IF(B2,B2*(IF(C2="r",1,(PI()*(D2/(VLOOKUP(C2,{"c",1;"d",2;"e",2;"s",2},2,0)))^(VLOOKUP(C2,{"c",1;"d",2;"e",2;"s",1},2,0)))*(VLOOKUP(C2,{"c",1;"d",1;"e",1.375;"s",1},2,0))))*(IF(ISNUMBER(E2),E2,1))*(IF(ISNUMBER(F2),F2,1))*(IF(ISNUMBER(G2),G2,1)),"")

I had a paren at the wrong place.

Aladin

===========


Posted by Bob on July 28, 2001 5:11 PM

Incredible reponse time, thanx much. Looks like it works with one exception, the calcs for h and s aren't cioming out right for some reason.
S, or spher, should calc as PI()* diameter squared, and (H)emi would calc as the sphere halved.
I can see I will be spending the rest of the night learning these new functions/calcs, thanx for hour help!


Posted by Aladin Akyurek on July 28, 2001 5:17 PM

Do you have a set of S & H numbers with expected results that I can look at?

Aladin I can see I will be spending the rest of the night learning these new functions/calcs, thanx for hour help! : Bob, : You need test it more


Posted by Aladin Akyurek on July 28, 2001 5:23 PM

Maybe, because...

I failed to include the "h" in the lookup array within VLOOKUP. With that included, we have:

=IF(B2,B2*(IF(C2="r",1,(PI()*(D2/(VLOOKUP(C2,{"c",1;"d",2;"e",2;"h",2;"s",2},2,0)))^(VLOOKUP(C2,{"c",1;"d",2;"e",2;"h",2;"s",1},2,0)))*(VLOOKUP(C2,{"c",1;"d",1;"e",1.375;"h",1;"s",1},2,0))))*(IF(ISNUMBER(E2),E2,1))*(IF(ISNUMBER(F2),F2,1))*(IF(ISNUMBER(G2),G2,1)),"")

Aladin

==========

Posted by Aladin Akyurek on July 28, 2001 5:45 PM

and ...

it appears I used wrong numbers (sigh) for the "s" case. I hope this is the final form:

=IF(B2,B2*(IF(C2="r",1,(PI()*(D2/(VLOOKUP(C2,{"c",1;"d",2;"e",2;"h",2;"s",1},2,0)))^(VLOOKUP(C2,{"c",1;"d",2;"e",2;"h",2;"s",2},2,0)))*(VLOOKUP(C2,{"c",1;"d",1;"e",1.375;"h",1;"s",1},2,0))))*(IF(E2,E2,1))*(IF(F2,F2,1))*(IF(G2,G2,1)),"")

One thing though: I didn't take up your formula for just one reason. I wanted to produce a formula with less nested IFs as much as possible.

Take care.

Aladin

Posted by Bob on July 28, 2001 5:54 PM

Re: Maybe, because...

Aladin:

The correct final values for S and H, based on a diameter of 10, would be 314.16 and 157.08 respectively, based on the formula of a sphere being PI*diameter squared, and the formula for a hemisphere being half that.
I am a newbie to Vlookup using tables, so by extension am absolutely blown away learning from you that it is possible to "string" the parameters within the formula itself vs messing with a table. With that in mind, am I interpreting correctly that the first "Vlookup" string in your formula is taking the diameter divided by either 1 or 2 to acheive either: no effect, or the radius? If that is the case then h and s would be treated like c, i.e no halving of the value?
Would you need to change the 1st Vlookup string then? The h value would have to be halved somehow after diameter is first squared, no?
Thanx again I failed to include the "h" in the lookup array within VLOOKUP. With that included, we have: =IF(B2,B2*(IF(C2="r",1,(PI()*(D2/(VLOOKUP(C2,{"c",1;"d",2;"e",2;"h",2;"s",2},2,0)))^(VLOOKUP(C2,{"c",1;"d",2;"e",2;"h",2;"s",1},2,0)))*(VLOOKUP(C2,{"c",1;"d",1;"e",1.375;"h",1;"s",1},2,0))))*(IF(ISNUMBER(E2),E2,1))*(IF(ISNUMBER(F2),F2,1))*(IF(ISNUMBER(G2),G2,1)),"") ==========

Posted by Aladin Akyurek on July 28, 2001 6:07 PM

Meanwhile

I looked at your formula & corrected it the way you wanted it. Eliminating the need for entering 1's in order to avoid #VALUE! errors.

Modified, it becomes:

=IF(B2,(IF(C2="c",PI()*D2,IF(C2="d",PI()*(D2/2)^2,IF(C2="e",PI()*(D2/2)^2*1.375,IF(C2="h",PI()*((D2^2)/2),IF(C2="r",1,IF(C2="s",PI()*D2^2))))))*B2*IF(E2,E2,1)*IF(F2,F2,1)*IF(G2,G2,1)),"")

As I said, I wanted to reduce the number of the nested IFs. I'll look at it further with the info that you give below.

Aladin

============= The correct final values for S and H, based on a diameter of 10, would be 314.16 and 157.08 respectively, based on the formula of a sphere being PI*diameter squared, and the formula for a hemisphere being half that.


Posted by Bob on July 28, 2001 6:22 PM

Re: Meanwhile

I came to this board to learn, and that I did. I am sticking with your original vlookup string and tweaked the h & s parameters in the 3 Vlookups just a little as follows:
=IF(B2,B2*(IF(C2="r",1,(PI()*(D2/(VLOOKUP(C2,{"c",1;"d",2;"e",2;"h",1;"s",1},2,0)))^(VLOOKUP(C2,{"c",1;"d",2;"e",2;"h",2;"s",2},2,0)))*(VLOOKUP(C2,{"c",1;"d",1;"e",1.375;"h",0.5;"s",1},2,0))))*(IF(ISNUMBER(E2),E2,1))*(IF(ISNUMBER(F2),F2,1))*(IF(ISNUMBER(G2),G2,1)),"")
Thank you very much for your help, and also this new formula for me to digest :)
Regards,
Bob I looked at your formula & corrected it the way you wanted it. Eliminating the need for entering 1's in order to avoid #VALUE! errors. Modified, it becomes: =IF(B2,(IF(C2="c",PI()*D2,IF(C2="d",PI()*(D2/2)^2,IF(C2="e",PI()*(D2/2)^2*1.375,IF(C2="h",PI()*((D2^2)/2),IF(C2="r",1,IF(C2="s",PI()*D2^2))))))*B2*IF(E2,E2,1)*IF(F2,F2,1)*IF(G2,G2,1)),"") As I said, I wanted to reduce the number of the nested IFs. I'll look at it further with the info that you give below. = : The correct final values for S and H, based on a diameter of 10, would be 314.16 and 157.08 respectively, based on the formula of a sphere being PI*diameter squared, and the formula for a hemisphere being half that.