Solved

Formula Revision - Lookup?

Posted on 2013-01-17
22
322 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 51

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
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
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
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
 
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
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
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

739 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