Setting up formula with too many "ifs" for my brain.

kellywy · May 19, 2017

Hi again --

Formulating recipes - and trying to set up a table that will auto-convert from oz. to grams, based on an ingredient and the initial rate of measurement from a recipe. And my brain's having none of it.

Here's what I have and what I need:

1) Column J6:J62 is a listing of Ingredients by name.

2) Column M6:M62 is a listing of the Grams equivalent for each ingredient by varying ounce/cup/tsp/T/ etc. measurements.

3) My calculating table is set up in A4:G20. Example:
a) A4 lists the ingredient name. (to match J6:J62 ingredient list)
b) B4 lists the quantity of measurement.
c) C4 lists the original recipe measurement type (Cups, Grams, tsp, etc.)
d) In D4 - I need to display the correct Grams for the recipe.

NOTE: Grams is the end-game. So, if C4 has the original recipe in Grams, I need D4 to just retain that number. But if C4 was in cups/tsp/etc. - I need to convert that to Grams.

Example 1:
A4 = "Flour"
B4 = 2.5
C4 = Cups
D4 = (needs to be the correct Grams measurement pulled from J6:J62 (ingredients) and M6:M62 (correct Gram conversion). That's M6:M62 Grams x B4 (Quantity of Measurement).

Example 2:
A4 = "Flour"
B4 = 240
C4 = Grams
D4 = (should remain 240 for Grams original measurement)

So far, everything I've tried has come up a bust. But if anyone has suggestions for the D4 formula, I'm all ears!

(And let me know if that all doesn't make sense....) Appreciate any assistance. Thank you!

Kelly

kellywy · May 22, 2017

OMG - it worked! (sorry for the delayed response....I was traveling over the weekend) I went back to my sheets and set them up the way you described (and yeah, my brain Really didn't like all Those words! haha!) Took me awhile to plug Flap A into Slot B - but by George, I think I've got it!

I'm testing it around with various recipes.....because I'm also trying to work in the Baker's Percentage calculations (but that should be easy, once I understand the concept).

This is so incredibly awesome - thank you for helping me with this, and for the very detailed explanation (because that's the only way I could have followed it)! It's solved a problem I've had for some months now with my business. Most bakers can use various software programs that are out there, but my biz doesn't fit in the mold of a typical bakery - and I have to create several new recipes each month. You have no idea what a time-saver this will be for me.

Thanks again, TotallyConfused (which SO should have been my user name.....) (and as for the celebrating.....I'd love to share - just don't know if I'm allowed to post my business name on here...gah!)

Kelly

FDibbins · May 22, 2017

Which 1 worked for you?

kellywy · May 23, 2017

Hi, Ford! It was the one from TotallyConfused: ‘=IF(A4="","",IF(C4="GRAM",B4,VLOOKUP(A4,Sheet2!A$2:Z$1001,VLOOKUP(C4,Sheet3!A$2:B$1001,2,FALSE),FALSE)*B4))

I did some rearranging of my data to match the detail provided, and it did the job!

FDibbins · May 23, 2017

Thanks for the update

TotallyConfused · May 24, 2017

Hi Kelly

WOW! I am flattered beyond belief that you would pick something I’d written to use. With all the extremely talented and experienced people here, the best way I can describe my feeling, is if I were in Hollywood and won an Oscar. With such kind words of praise from you, my feet probably won’t touch the ground for a month of Sundays.

You mentioned you were planning to try to work in something called ‘Baker’s Percentage’ calculations. I have no idea what that is, but if you could describe it, is there any way I could lend a hand in adding it? I know this is being selfish of me, but now that I sort have a little bit of a vested interest in what seems to be an ongoing project for you, I’d kind of like to see it through to the end, that is if you don’t mind. It would be good practice for me and maybe I’d learn a little more about Excel and baking, which I’ve already done. I hadn’t realized that different types of flour would have their own weight per unit of measurement. I hope that I’d be able to free up more of your time to allow you to do what you do, that of creating recipes for people to enjoy.

As far as us celebrating the success of my code, that was just a silly thought that popped into my head at the last moment, (I get those from time to time

) so I added it. It’s not as if I live across the street from you and can drop over any time, because I live about 300 miles or so, east of you. It’s been a long time since I’ve been in your city. By now the downtown area has probably changed so much, I wouldn’t recognize it. Knowing that my work has made life easier for you is a big reward in itself. Reading your ‘thank you’ note made my whole day, so that piece of cake or slice of pie will have to wait until a later time. Thank you for the offer anyway.

TotallyConfused · May 24, 2017

Update to the above:

After I posted this, I kept thinking about ‘Bakers Preference’ and wondering what in the world that could be. Finally, curiosity (which I have an unlimited amount of) got the best of me, and I did what I should have done in the first place. I went to the internet to see if I could find anything there. I’ve only started to read through one of the links, but this is very interesting. The more I read, the more intriguing it’s becoming. Now Kelly, you need to know one thing about me, and that is that this is coming from a guy whose claim to being a cook/chef/baker comes from whatever he can open up and then put in his microwave oven. No kidding.

While I don’t fully understand it yet, it occurs to me there should be some formula that would allow a person to modify a recipe. By that, I mean, if you know the quantity of ingredients it takes to bake a dozen loaves of bread, then what would it take to bake 25 loaves. I’m not a mathematician, but that should work in reverse too. How much would you need to bake five loaves? When I get time, I’m going to read more and play around with some numbers until I figure out how this works. Hey, I didn’t really want to watch that movie anyway.

kellywy · May 24, 2017

Haha! No worries on that one. It's a little more complex just to understand the concept - the Excel part was easy once I did. But yes, being able to scale up or down on recipes is a huge help - hence, the need to convert to grams. So you got the hard part done...(at least on the computer!). Now it's a matter of getting out the ingredients and getting to work! You crack me up. But trust me - baking is a lot more relaxing than Excel, for me, anyway. Thanks again!

TotallyConfused · May 27, 2017

Hello Kelly

So you think I am funny, do you?

I guess at times I may have my moments, but I’m not a comedian. If you were to meet me, you would soon realize that most of the time I’m fairly quiet. I can be in a crowd of people, and yet let someone else be the center of attention and still be happy. Some people seem to crave that attention all the time, but I don’t seem to need it. I can speak up, and have a few times when I feel the need to.

As I promised, I've been playing around with a few ways to see if could add your Bakers Preference to that first table I sent you, the one you enter all the ingredients on. By now, you probably have that table already set up. If so, it will be interesting to see if my inexperienced mind can come up with something even close to what you have. I’ve thought of a couple ideas, but I need a little more information from you.

1) Would you ALWAYS enter the flour first? (I'm hoping so)
2) When entering the name, do you ALWAYS add the word ‘flour’? For example, would you list it as All-purpose flour or simply All-purpose?
3) Would there ever be a time when you would use more than ONE type of flour? (I’m hoping the answer is ‘no’).
4) When I sent my solution to you, I referred to Sheet1, Sheet2 and Sheet3. Have you kept those three names? If you renamed them, it would help if I knew what they were so I could refer to them and avoid any confusion.

Now, as they say after any news story that is still unfolding, stay tuned for further updates as they happen. In this case, that could and probably will, include any questions and or some of my silly humor.

kellywy · May 27, 2017

Haha! Yes - I actually did get the table created, and it only had to be set up on the actual main sheet. (I added 3 columns to the right of the initial calcs that you helped me set up - Columns E=% of Recipe, F=Scale % Measure, G=Grams for the new measure.) But in answer to your questions:

1) Yes, flour would always be first - and it's the 100% item that the other figures calculate off of.
2) When I enter the name, to avoid any typo errors or not remembering the specific flour title (since there are about 20 or so), I simply reference the line item on the other page.
3) Yes, there would be times I use more than one type of flour, but the highest volume flour in the recipe would be first and 100%.
4) The sheet names are now: Sheet1 = GB Calcs Sheet2 = Ingredients Sheet3 = Measure.

Appreciate your continued interest in this craziness! Since baking doesn't fit easily into such calculations, I'll be interested to learn what you come up with!

Thanks mucho!

Kelly

TotallyConfused · Jun 2, 2017

Hello Kelly<o

></o

>
<o

> </o

>
Thank you for your prompt reply to my questions. The thoughthad occurred to me that since your original problem had been solved, perhaps youwouldn’t be coming back here. I hope you haven’t been thinking I've ‘desertedyou’ because it has been a few days since I last posted something. There’s nota chance of that happening because I still expect that special cake or pie youbake from my formula. J Thingsaround here have piled up on me the last couple of days, and I’ve gotten behindon this, my pet project. <o

></o

>
<o

> </o

>
I see I was correct in assuming you had already set up yourBakers Preference table. That’s great and you can be proud of yourself forhaving done so. By adding your three new columns to that main sheet, you arealready acting upon the vision I had when I set the whole thing up. We canexpand that table to include an almost unlimited amount of information, withlittle or no change to the other two. They have to be there because theyprovide a support service. In case you are wondering why I didn’t put those twotables on the same sheet, it’s because of this potential for expansion. Ididn’t want it to be running up against one of the others at some future date. It’spossible to insert extra columns, and in most cases, there will be no problem,but not always.<o

></o

>
Ever since I started working on this project, I’ve tried tokeep in mind to make it as open ended as possible, so that you never come upagainst an upper limit of something that can’t be extended.<o

></o

>
<o

> </o

>
That is a very good idea you have about how you list theingredients on the GBCalcs sheet by reference to the INGREDIENTS sheet. Thisensures everything is the same because even an extra space from the space bar atthe end of either the GBCalcs or the INGREDIENTS sheet would cause everythingto fail. Before I knew you were doing that, I’d started my formula to filter outthat type of error, however, a simple typing error… well, you’re on your ownthere.<o

></o

>
<o

> </o

>
I have no idea how you coded those three columns you added, soin order to see if my idea for the Baker’s Preference idea will work, I’m goingto ask you to perform a little experiment. I’d like you to load in some sampledata, and then insert a new column to the left of what is now your ‘A’ column. Ifyou aren’t sure how to insert a column, it is quite simple. Put your cursor onthe ‘A’ for Column ‘A’ and press the RIGHT mouse button. Then LEFT click on INSERT.What used to be Column ‘A’ is now ‘B’ and the column where my formula is willnow be ‘E’. Did any of your data change? I hope not.<o

></o

>
<o

> </o

>
To get rid of that extra column, you can either exit withoutsaving or put your cursor on ‘A’ of this new column and press the LEFT button.That column should now be highlighted. RIGHT click on ‘A’ then scroll down andLEFT click on Delete, and it should be gone. Move the cursor to any blank celland LEFT click to get rid of the highlighted column.<o

></o

>
<o

> </o

>
Since you did such a great job of that, I have a few questionsfor you.<o

></o

>
1) In an earlier post Ihad asked how you entered the names of the ingredients on the GBCalcs sheet.You said you link to the INGREDIENTS sheet. So now I’m asking how you enteredthe data on the INGREDIENTS sheet, in particular I’m interested in any flourentries. For example, did you list All-purpose flour as ‘All-purpose flour’ orsimply as ‘All-purpose’? The reason I’m asking, is that in order to determinewhich entries are flour and which are other items, I scan the name and look forthe word ‘FLOUR’ (doesn’t matter if upper or lower case). It doesn’t have to bethat word, that was the first thing I thought of that wouldn’t be used with anyother item. If you don’t want to type that for every type of flour, I canadjust the code to search for anything. I just need something unique to anyflour entry. It could be at the beginning, somewhere in the middle or at theend.<o

></o

>
<o

> </o

>
2) You said that you hadadded Column E, F, and G to the GBCalcs sheet. I’m a little confused about whatColumn F = Scale % Measure would include and what would you use that data for.How did you calculate it?<o

></o

>
<o

> </o

>
3) The same for Column G= Grams for the new measure. When I first read that, the thought occurred to methat it would be the same as my formula gave in Column D but obviously not. Isthis column used as part of the calculation needed to scale up a recipe, sayfrom enough for one loaf of bread to seven loves?<o

></o

>
<o

> </o

>
4) Have you added any columnsto the table on the INGREDIENTS sheet? If so, you probably had to add to theMEASURE table also. I think it will help avoid future confusion if we can keepthings the same for both of us.<o

></o

>
<o

> </o

>
I do have a procedure worked out to calculate the BakersPreference, but I’ll wait to see the result of that experiment and your answerto question 1, before I send it to you, in case I need to make someadjustments.<o

></o

>
<o

> </o

>
I’d love to know how you coded your Column E - % of Recipe.<o

></o

>

Setting up formula with too many "ifs" for my brain.

kellywy

Board Regular

kellywy

Board Regular

Excel Facts

FDibbins

Well-known Member

kellywy

Board Regular

FDibbins

Well-known Member

TotallyConfused

Board Regular

TotallyConfused

Board Regular

kellywy

Board Regular

TotallyConfused

Board Regular

kellywy

Board Regular

TotallyConfused

Board Regular

Similar threads

Forum statistics

Share this page

Setting up formula with too many "ifs" for my brain.

Board Regular

Board Regular

Excel Facts

Well-known Member

Board Regular

Well-known Member

Board Regular

Board Regular

Board Regular

Board Regular

Board Regular

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock