Formula Revision - Lookup?

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
LVL 1
GeekamoAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
leptonkaConnect With a Mentor Commented:
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
 
Michael FowlerSolutions ConsultantCommented:
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
 
Rgonzo1971Commented:
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
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
GeekamoAuthor Commented:
@ 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
 
leptonkaCommented:
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
 
GeekamoAuthor Commented:
@ Rgonzo1971,

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

~ Geekamo
0
 
GeekamoAuthor Commented:
@ 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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
GeekamoAuthor Commented:
@ teylyn,

This isn't working correctly either.

~ Geekamo
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
GeekamoAuthor Commented:
@ teylyn,

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

~ Geekamo
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
GeekamoAuthor Commented:
@ 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
 
GeekamoAuthor Commented:
@ 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
 
Saqib Husain, SyedEngineerCommented:
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
 
GeekamoAuthor Commented:
@ ssaqibh,

Nope, that doesn't work correctly.
0
 
GeekamoAuthor Commented:
@ All

Thank you all for your input!
0
 
Saqib Husain, SyedEngineerCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
GeekamoAuthor Commented:
@ 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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
GeekamoAuthor Commented:
Oh wow, that's great! And thanks! :)

~ Geekamo
0
All Courses

From novice to tech pro — start learning today.