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

FDibbins · May 19, 2017

As a start on this, have you tried using the CONVERT() function yet?

FDibbins · May 19, 2017

hmm doesn't seem to be a 'cup" to "gram" conversion

Do you have a conversion factor for each measure-to-grams? If so, you could make up a small table and use VLOOKUP(), then apply that factor to the volume?

kellywy · May 19, 2017

Hmm. No, I haven't, actually. But I'm not sure how that would work. Problem being that all ingredients have different weights (per the M6:M62 column), so I assumed that would be the only way to account for them accurately....?

Example: 1 cup of all-purpose flour = 120 grams; but 1 cup of hazelnut flour = 89 grams, 1 cup of unsweetened cocoa = 85 grams, etc. And the conversion rates will continue to be added to over time with new ingredients, etc. - so this list will just keep getting longer.

Is there a way to make that work?

kellywy · May 19, 2017

Unfortunately, VLOOKUP isn't my strong suit - and when I try to mix in the "if cups - convert to grams, but if grams, retain the number", that's when my brain starts smoking out the ears.

FDibbins · May 19, 2017

This is kinda what Im thinking of...

	A	B	C	D	E	F
1					all-purpose flour	120
2	blah 1	20			hazelnut flour	89
3	blah 3	60			unsweetened cocoa	85
4	hazelnut flour	89			blah 1	20
5	blah 8	300			blah 2	50
6					blah 3	60
7					blah 4	12
8					blah 5	55
9					blah 6	88
10					blah 7	99
11					blah 8	300

Your "measures" will be in columns E:F, and you can add to that as needed.

A2 would be your "entry"
B2=IF(A2="","",VLOOKUP(A2,$E$1:$F$1000,2,0))
copied down

I have made allowances for the formula to look at 1000 rows of data in E and F, and you can expand that if needed

Eric W · May 20, 2017

Ford, a cup is a unit of volume, a gram is a unit of weight, so they don't convert directly. If you know the density of the substance you want to convert, you can do it, which is essentially what the conversion table is.

Kelly, if I understand your layout properly, it looks something like:

Excel 2012

	A	B	C	D	J	K	M
1
2
3				grams
4	flour	2.5	cup	300
5	flour	240	gram	240
6	flour	10	tsp	25	flour	cup	120
7	unsweetened cocoa	5	tsp	8.854167	salt	cup	273
8	baking soda	10	tsp	45.83333	hazelnut flour	cup	89
9				0	unsweetened cocoa	cup	85
10				0	baking soda	cup	220
11					flour	tsp	2.5
12					salt	tsp	5.6875
13					hazelnut flour	tsp	1.854167
14					unsweetened cocoa	tsp	1.770833
15					baking soda	tsp	4.583333
16

Sheet1

Array Formulas

Cell	Formula
D4	{=IF(C4="gram",B4,INDEX($M$6:$M$62,MATCH(A4&"\|"&C4,$J$6:$J$62&"\|"&$K$6:$K$62,0))*B4)}

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

The part I couldn't figure out is how to compare the unit you have in column C, with the ingredient in column J. In order to handle that, I added a column K. Once that's in place, the formula in D4 should work for you. Note that the unit must match, "cups" doesn't equal "cup". If you have a different way to match up the units, let us know.

Hope this helps!

FDibbins · May 20, 2017

Eric W said:
Ford, a cup is a unit of volume, a gram is a unit of weight, so they don't convert directly.

hmm yup, good point. Metric kinda gets around that, conversions between different media is simpler

That is why I went for a table and vlookup (like you did). I just didn't take it further and apply it to the "unit" qty

FDibbins · May 20, 2017

Thinking again, we would need to combine the product and measure, maybe in a helper, then vlookup on that?

TotallyConfused · May 21, 2017

Hello Kellywy

This is quite a question you have posed for everyone and from your description of what you have, it sounds as if you have a very elaborate setup. I’m no expert on Excel, actually I’m trying to learn it, however, I think I have come up with an idea that I believe will give you what you are asking for and in a much simpler way. For example, you mentioned that you were using so many ‘IF’ statements that your ‘brain’s having none of it’. (I love your sense of humor) You may be interested to know that I’ve used only two ‘IF’ statements.

ON SHEET 1:

	A	B	C	D
1
2			ONE
3	INGREDIENT	QUANTITY	UNIT SIZE	GRAMS NEEDED
4	All-purpose flour	91	gram	91
5	abc	3	CUP	33
6	ABC	6	tsp	30
7	Unsweetened cocoa	0.5	Cup	42.5
8	HazelNUT flouR	0.75	cuP	66.75
9	brown sugar	1	oz	29

Above is where I set up what I believe you are calling your ‘Calculating table’ starting in A4. I kept the same row and column numbers as you mentioned. I designed everything so all you have to do is enter the data in Columns ‘A – B – C’. The formula in Column D does the conversion from the recipe units of measurement to grams.

Column D has the only formula and I designed it to allow for a great deal of expansion. You have room for up to 25 different units of measurement (See explanation for Sheet 3) and 1,000 different ingredients (See explanation for Sheet 2). One of the two ‘IF’ statements deal with your desire to have the value in Column B transferred to Column D if the recipe already used ‘GRAM’ as its unit of measurement. The other ‘IF’ statement keeps Column D clear if some rows are not used. I have entered a few random items to show that while correct spelling is important, upper/lower case text does not matter. Write the items in Column C in the singular, e.g. CUP and not CUPS.

After you are finished with one recipe and want to remove all the figures, clear the contents of Columns A, B, C, *WARNING* DO NOT do anything with Column D, As soon as any entry in Column A is erased, the corresponding entry in Column D will disappear. If you need to add more ingredients but nothing shows up in Column D, first copy the last cell from Column D down for as many rows as you need, and then enter the remaining ingredients.

Here is that magical formula that does everything from Column D. Copy it into D4. I wrote this to work from Sheet2 and Sheet3. If you wish to use your own names, carefully delete only the words ‘SHEET2’ and ‘SHEET3’ then substitute your names. Then delete the apostrophe at the beginning, and copy it down a few rows.

‘=IF(A4="","",IF(C4="GRAM",B4,VLOOKUP(A4,Sheet2!A$2:Z$1001,VLOOKUP(C4,Sheet3!A$2:B$1001,2,FALSE),FALSE)*B4))

ON SHEET 2:

	A	B	C
1	INGREDIENT	GRAM/TSP	GRAM/CUP
2	All-purpose flour	10	120
3	Hazelnut flour	7	89
4	Unsweetened cocoa	30	85
5	ABC	5	11
6	DEF	5	105

The above table shows the number of grams per different units of measurement for varying ingredients. If you wish to add another INGREDIENT or add another unit of measurement, this is the first place to come to. If adding another INGREDIENT, fill in the next available row, or if you are adding a new measurement, go to the next available column and enter the information. The column headings are there only for documentation, and have no meaning as far as the formula in SHEET 1 is concerned. You can expand this table out to 25 columns of measurements (Column Z) and down to row 1001 without having to adjust the formula in COLUMN D from SHEET1).

For example, we’ll say you want to add Brown sugar to your list of INGREDIENTs. Type Brown sugar in ROW 6 and add the corresponding weights of GRAMS/TSP and GRAMS/CUP for Brown sugar. While you are here editing this table, we’ll assume you decide to add another column of GRAMS/OZ for each ingredient. You would put this data in the next available column, in this case COLUMN D. You do need to remember the name of the new INGREDIENT, and that the new column of GRAMS/OZ, (or whatever you called it) is Column 4 in order to complete the next and final step. Now proceed to Sheet 3.

ON SHEET 3:

	A	B
1	MEASURED UNIT	COLUMN #
2	TSP	2
3	CUP	3
4	OZ	4

Upon arriving here, type in the name of the new unit of measurement you entered the same way you typed it in the last table and also enter the number ‘4’, in COLUMN B. The purpose of this table is to tell the formula in SHEET1 what column to look at in the table from SHEET2, in order to get the correct number of GRAMS per whatever UNIT SIZE you specified in COLUMN B on SHEET1.

There should never be a need to add new columns here, but in keeping with the ability to increase the number of ROWS in the table on SHEET2; you may also extend this table down to row 1001 with no adjustment to the formula on SHEET1. It’s highly unlikely you would ever have that many UNITs OF MEASUREMENT to enter.

You have completed the process of entering any new INGREDIENTS and their corresponding weight. You may now return to SHEET1, at which point I STRONGLY recommend you save these changes you have made. You may now enter the INGREDIENT of Brown sugar in the normal manner.

If you had already tried to enter this item without realizing it was NOT part of SHEET2 and SHEET3, you would have received a #NA error message. If this is what happened, the #NA error message should now be gone and in its place the number of grams.

I hope this will be of some help to you. There is one more thing you can do if you decide this is something that you can use. When you bake your first cake or pie by using the information supplied from here, you can invite me over and together we’ll celebrate this programs initiation. Doesn’t that sound like a good idea? It does to me and my stomach thinks so also. HaHa.

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

kellywy

Board Regular

Excel Facts

FDibbins

Well-known Member

FDibbins

Well-known Member

kellywy

Board Regular

kellywy

Board Regular

FDibbins

Well-known Member

Eric W

MrExcel MVP

FDibbins

Well-known Member

FDibbins

Well-known Member

TotallyConfused

Board Regular

Similar threads

Forum statistics

Share this page

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

Board Regular

Excel Facts

Well-known Member

Well-known Member

Board Regular

Board Regular

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

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