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

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.

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

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.

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

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

Michael

You could try this

=IF(COUNT(O17:Q17)=2,"Bag Size: "&ROUND(((IF(O16="Diameter

Regards

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

You can modify your formula this way:

=IF(COUNT(O17:Q17)=2,"Bag Size: "&ROUND((O17*IF(O16="Diame

Cheers,

Kris

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?

=IF(COUNT(O17:Q17)=2,"Bag Size: "&ROUND(IF(O16="Diameter",

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

cheers, teylyn

Book1.xlsx

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

=(IF(O16="Diameter",((O17*

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

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

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

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

=IF(COUNT(O17:Q17)=2,"Bag Size: "&ROUND(((O17*PI()^(2*(O16

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="Diam

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="Diame

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="Di

The complete formula for O19 is

=IF(COUNT(O17:Q17)=2,"Bag Size: "&ROUND(((O17*IF(O16="Diam

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

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

All Courses

From novice to tech pro — start learning today.

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