LinkBack URL
About LinkBacks
im trying to write a formula. Its area calculations.(L x W=A), (L x H=A),((L*2+W*2 x H=A)
Ex:A1,B1,C1,D1 are all input cells. I need D1=A1*B1 and/or D=A1*C1 and/or D1=(A1*2+B1*2)*C1 and/or D1=D1
Basically 4 possible variations
im trying to write a formula. Its area calculations.(L x W=A), (L x H=A),((L*2+W*2 x H=A)
Ex:A1,B1,C1,D1 are all input cells. I need D1=A1*B1 and/or D=A1*C1 and/or D1=(A1*2+B1*2)*C1 and/or D1=D1
Basically 4 possible variations
Last edited by gcajnr21; 04-25-2011 at 01:55 PM.
See if this what you meant:
In precedence:
if D1 contains a value show this value regardless of values in A1:C1
If A1:C1 all contain a value, then calculate and show this result
Else, calculate using A1 and max value between B1 and C1
=IF(D2>0,D2,IF(COUNT(A2:C2)=3,((A2*2)+(B2*2))*C2,A2*MAX(B2,C2)))
Palmetto
Do you know . . . ?
You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.
Thanks I got this to work by taking off "D2>,0,D2,IF" so "=IF(COUNT(A2:C2)=3,((A2*2)+(B2*2))*C2,A2*MAX(B2,C2))" but now I have another issue?Originally Posted by Palmetto
Since the formula is in cell D2 there was no way to manually input a value to D2. So now I need to Add E2 to = D2? This way if I know the value of D2 I can input it in E2.
Again thanks for your time, your awesome!!
Need more help on this one. I need E2=D2 as well.
Since the formula is in cell D2 there was no way to manually input a known number to D2. So now I need to Add E2 to = D2. This way if I know the value of D2 I can input it in E2.
This make sense?
Put the original formula I gave you into E2.
I assumed, when writing the formula, that D2 would be a manual input so that is why it is tested first.
If that doesn't do it, then upload a sample workbook that shows what you need.
I figured out what I really needed? I attached my sample worksheet.
My worksheet has different cell numbers.
I need K2 to get value from cells F2 and/or G2, and Add F2+G2. This will allow me to input into G2 if I know the value, or I can have it get the value from F2, and also Add F2+G2 if I need it too.
In the meantime Ill be researching this to try and figure it out on my own. Thanks for your time!
Last edited by gcajnr21; 04-25-2011 at 01:10 PM.
Can anyone help with this, my brain is starting to hurt? I tried
I've been looking at your file and I can't figure out what you're asking.
Your sample file should give examples showing the results you expect to get and why.
Your formula in F2 is: =IF(G2>0,G2,IF(COUNT(C2:E2)=3,((C2*2)+(D2*2))*E2,C2*MAX(D2,E2)))
so as soon as you put a value in G2 you'll get that same value in F2 so essentially adding F2 and G2 is the same as =IF(G2>0,G2*2,F2)
On a side note you have several unnecessary SUM() functions in the range I5:I10
and the formula in J2:
=IF(I2=1,H2*0.8,IF(I2=2,H2*0.6,IF(I2=3,H2*0.4,IF(I2=4,H2*0.2,H2))))
can be simplified to:
=H2*(1-I2*0.2)
I think your formula in F2 could be simplified from:
=IF(G2>0,G2,IF(COUNT(C2:E2)=3,((C2*2)+(D2*2))*E2,C2*MAX(D2,E2)))
to
=SUM(C2:D2)*COUNT(C2:D2)*E2 without the tie in to G2
and G2 would always be equal to E2 wouldn't it? (I'm assuming SF/LF means Square Feet per Linear Foot)
I don't see why K2 wouldn't be: =IF(J2=0,0,F2/J2)
Last edited by Cutter; 04-24-2011 at 01:38 PM.
Hi Cutter thanks for your response.
Im trying to make Cell C2,D2,E2 an area calculator. So if I input either LxW or LxH or LxWxH it will give me the SUM. This is good when Im out in the Field without a calculator, but if I know or someone gives me the total square footage or linear footage I can just enter it in cell G2 without the need for the calculator.
Then on top of that, say I use the calculator and get the SUM and I want to add an extra 50sqft then I can just enter it into G2.
Does this make sense? Thanks
So you want the Square Foot calculator cells (C:E) to be optional.
You want G2 to be the alternative to F2 and G2 will be manual entry only.
If something is in G2 and nothing in F2 calculate based on G2.
If something in F2 and nothing in G2 calculate based on F2.
If something in F2 and something in G2 calculate based on SUM(F2:G2).
Why would putting the linear foot value in G2 be helpful without knowing the Height?
Would you also need to base a calculation using G2 value on the value of E2?
If E2 is 0 then G2 is area but if E2 > 0 then G2 is linear feet.
Does that sum up what you want?
If so, try the attached:
Last edited by Cutter; 04-24-2011 at 04:34 PM. Reason: Added sample file
Thanks, I found one problem...If I have, say 6 in F2 and 6 in G2 I get the calculated Hrs in K2, but when I put 12 only in G2, I get different calculated Hrs? This ends up with wrong totals. Any ideas?
Everything else seems to be working just as I needed. Thanks tremendously!
I dont need the height.
No
The formula in K2 is based on what I said in my previous post.
Try changing it to:If E2 is 0 then G2 is area but if E2 > 0 then G2 is linear feet.
=IF(J2=0,0,SUM(F2:G2)/J2)
This will give result strictly based on this:
So if you now have 6 in F2 AND 6 in G2 it will give same result as 12 in F2 OR 12 in G2If something is in G2 and nothing in F2 calculate based on G2.
If something in F2 and nothing in G2 calculate based on F2.
If something in F2 and something in G2 calculate based on SUM(F2:G2).
Okay that worked but I found the problem in O2.
The formula in O2 is wrong, throwing off my totals when I add a value to P2?
This is the first time you've mentioned the formula in O2 so I don't know what it is that you want there.
Based on what you had there, would it be:
=IF(N2=0,0,SUM(F2:G2)/N2)
That did it and worked! Thanks so much
You're welcome.
Don't forget to mark your thread as SOLVED (click FAQ at top of page for directions if needed).
Thanks for the "scales tap".
Last edited by Cutter; 04-25-2011 at 05:30 PM. Reason: Added thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks