Solved

Formula Revision - Lookup?

Posted on 2013-01-17
22
289 Views
Last Modified: 2013-01-18
Hello Experts,

The longer my formula becomes, the harder it is for me to edit this.  I think I'm to a point where I need a lookup formula for what I'm looking to accomplish.

Screenshot
Cell O19, is using this formula

=IF(COUNT(O17:Q17)=2,"Bag Size: "&ROUND(((O17*PI())/2+1), 0)&"""W x "&Q17+O17+5&"""L","")

Cell O17, always wants a diameter value.  But there are times, were I know my circumference and not the diameter.

What I am planning, is putting a drop down into cell O16, "Diameter" & "Circumference" will be the only two choices.

This is far beyond my knowledge of formulas, so I have no idea how to update my formula, to accommodate for the change of cell O16.

For example...

If cell O16 says "Diameter", then my formula up above will be used.

BUT

If cell O16 says "Circumference", this new formula needs to be used.

=IF(COUNT(O17:Q17)=2,"Bag Size: "&ROUND(((O17/PI())/2+1), 0)&"""W x "&Q17+O17+5&"""L","")

The only thing that changed, is the * into / before my PI() function.

I'm hoping that someone can help me along with this.

Thank you in advance for your help!

~ Geekamo
0
Comment
Question by:Geekamo
  • 11
  • 5
  • 2
  • +3
22 Comments
 
LVL 23

Expert Comment

by:Michael74
ID: 38791897
When you place the combobox into the sheet you then capture the change of the drop down value and update the formula accordingly.

Private Sub ComboBox1_Change()
   If ComboBox1.Value = "Circumference" Then
      Range("O19").Formula = "=IF(COUNT(O17:Q17)=2,""Bag Size: ""&ROUND(((O17/PI())/2+1), 0)& """"""W x "" & Q17+O17+5 & """"""L"","""")"
   Else
      Range("O19").Formula = "=IF(COUNT(O17:Q17)=2,""Bag Size: ""&ROUND(((O17*PI())/2+1), 0)& """"""W x "" & Q17+O17+5&""""""L"","""")"
   End If
End Sub

Open in new window


Michael
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 38791904
Hi,

You could try this

=IF(COUNT(O17:Q17)=2,"Bag Size: "&ROUND(((IF(O16="Diameter";O17*PI();O17/PI())/2+1), 0)&"""W x "&Q17+O17+5&"""L","")

Regards
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38791907
@ Michael74,

Is this possible without the use of code?

If your solution is the better approach, instead of having a formula do the work - can you explain a little further how I use it?

For example, I assume I drop that code into the module window.

As for my drop down list, I was planning on using just the basic list and (not an actual combo box).

Does your code, require the user to Enable Macro's?

~ Geekamo
0
 
LVL 7

Expert Comment

by:leptonka
ID: 38791910
Hi,

You can modify your formula this way:

=IF(COUNT(O17:Q17)=2,"Bag Size: "&ROUND((O17*IF(O16="Diameter",PI(),1/PI())/2+1), 0)&"""W x "&Q17+O17+5&"""L","")

Cheers,
Kris
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38791911
@ Rgonzo1971,

This formula contains an error.  It's not working for me.

~ Geekamo
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38791916
@ leptonka,

Hmm, this is not returning the value I'm expecting.

For example...  If my Diameter is 12, the formula returns - Bag Size: 20"W x 31"L

12" Diameter, would be 38" Circumference

So if I change the word to Circumference, and put 38 into cell O17, then I would expect to see the same answer as before - Bag Size: 20"W x 31"L

Am I missing something here?
0
 
LVL 50

Expert Comment

by:teylyn
ID: 38791928
Try

=IF(COUNT(O17:Q17)=2,"Bag Size: "&ROUND(IF(O16="Diameter",((O17*PI())/2+1),((O17/PI())/2+1)), 0)&"""W x "&Q17+O17+5&"""L","")

See attached. I've copied your original formulas in the yellow cells to check the results.

cheers, teylyn
Book1.xlsx
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38791938
@ teylyn,

This isn't working correctly either.

~ Geekamo
0
 
LVL 50

Expert Comment

by:teylyn
ID: 38791940
If a formula becomes too long and unwieldy, it may be a good idea to break it up into separate cells.

For example, in cell M19, calculate the "W" value

=(IF(O16="Diameter",((O17*PI())/2+1),((O17/PI())/2+1)))

In cell N19 calculate the "L" value:

=S17+O17+5

Then, in cell O19 put the two numbers together

="Bag Size: "&ROUND(M19,0)&"""W x "&S17+O17+5&"L"""

See attached.

cheers, teylyn
27999975.xlsx
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38791946
@ teylyn,

Your workbook doesn't return the value I am expecting.

~ Geekamo
0
 
LVL 50

Expert Comment

by:teylyn
ID: 38791950
The formula got wrong references when I inserted columns in the sheet.

See the attached file for the all in one formula in O19 and the simplified version in O20.

If that does not help, please post a copy of your file.
27999975-new.xlsx
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:Geekamo
ID: 38791971
@ teylyn,

Ya, this isn't working either.

I have attached an example Workbook with some additional notes.  Either I am clarifying things, or confusing things even further. :)

~ Geekamo
TestForEE.xlsx
0
 
LVL 7

Accepted Solution

by:
leptonka earned 500 total points
ID: 38792063
Hi,

I attached what I think based on your sample file.
I created a support cell calculating the diameter to make it evident.
I guess you need to change the L part of the formula too.
I also added a formula without using the support cell.
Is it what you would like to see?

Cheers,
Kris
TestForEE-formula.xlsx
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38792078
@ Kris,

You hit it right on the head, and boy oh boy is that formula huge! lol

Thank you very much for your help, I appreciate it!

~ Geekamo
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38792093
Try this formula

=IF(COUNT(O17:Q17)=2,"Bag Size: "&ROUND(((O17*PI()^(2*(O16="Diameter")-1))/2+1), 0)&"""W x "&Q17+O17+5&"""L","")
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38792112
@ ssaqibh,

Nope, that doesn't work correctly.
0
 
LVL 1

Author Closing Comment

by:Geekamo
ID: 38792114
@ All

Thank you all for your input!
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38792120
Geekamo,

You got all those wrong results because the second formula that you provided in the question stating "replace * with /" was wrong.

The correct formula the shortest I could make it would be

=IF(COUNT(O17:Q17)=2,"Bag Size: "&ROUND(((O17/IF(O16="Diameter",1,PI())*PI())/2+1), 0)&"""W x "&Q17+O17+5&"""L","")
0
 
LVL 50

Expert Comment

by:teylyn
ID: 38792125
Hello,

This is not about Excel but about maths. I'm not into that very much, but I think you have a flaw in your logic.

If O17 = 12 (and the unit is diameter, as specified in O16), then, yes, =O17*PI() will return the circumference of 38 (rounded)

BUT if the unit in O16 specifies circumference, then you need to divide the value 38 by PI(), and not the value in O17, which is still 12. So, if you change the UNIT from Diameter to Circumference, you also need to change the unit VALUE to the respective number.

So, specify Diameter in O16 and the value is 12, then you calculate the circumference with =O17*PI()

But if the unit of measure is Circumference, then you don't need to calculate the circumference, because you already have it. It's the value entered in O17.

Therefore, the formula to calculate the "W" should be

=ROUND(((O17*IF(O16="Diameter",PI(),1))/2+1), 0)

If O16 is Diameter and O17 is 12, then the result is 20.
If O16 is Circumference and O17 is 38, then the result is also 20.

Likewise, the value for "L" needs to be adjusted, depending on the unit of measure and the value in O17.

If =Q17+O17+5 returns the correct value for a diameter of 12, then you need a different calculation if O17 shows a circumference. In that case, you need to take the circumference value and divide it by PI() to arrive at the diameter.

So, to calculate "L" for circumference and diameter, depending on the selection in O16, use

=ROUND(Q17+(O17/IF(O16="Diameter",1,PI()))+5,0)

The complete formula for O19 is

=IF(COUNT(O17:Q17)=2,"Bag Size: "&ROUND(((O17*IF(O16="Diameter",PI(),1))/2+1), 0)&"""W x "&ROUND(Q17+(O17/IF(O16="diameter",1,PI()))+5,0)&"""L","")

Remember: If you change the unit of measure in O16, you need to adjust the number in O17!!

cheers, teylyn
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38795319
@ teylyn,

Thank you very much for taking the time to explain this to me.  Math and Excel are not my strong suits. lol But I am trying to absorb more and more.  I'm surprised with myself that I was able to write the portion I did myself. :)  The longer it gets, and the more things it references the more my brain can't take it all in.  I've been watching videos on YouTube from a guy named Mr. Excel and I'm amazed at like how fluid he is writing formulas.  It's just like second nature. I had a version of this spreadsheet before, and it was so simple but used supporting cells so none of my formulas were anything beyond basic. I can't wait until my spreadsheet is finished, I'm super excited!

Thank you again!

~ Geekamo
0
 
LVL 50

Expert Comment

by:teylyn
ID: 38795699
Hello Geekamo,

I'll meet Mr Excel in a few weeks and I'll pass on your comments. Excel can be an exciting journey. Don't worry if you feel at times that it does your head in. We've all been there, even Mr Excel.

cheers, teylyn
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38795704
Oh wow, that's great! And thanks! :)

~ Geekamo
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now