I have a recipe development calculator that I’d like to be able to open on machines that run excel. Everything opens up fine, except a specific formula breaks down.
=SUMPRODUCT($D$53:$D$56*VLOOKUP($B$53:$B$56,$'Data '.$A$2:$'Data '.$I$100,5,0))
Here is an example of the formula, which is used extensively through the document in different iterations.
the idea is that it takes an ingredient, finds the relevant data for that ingredient in a table (calories per gram) and then multiplies that by the weight.
e.g.
flour 65g
find flour in the table, flour is 3.48 calories per gram, multiply 65 by 3.48, then repeat for the rest of the ingredients, and sum up the total. Then you get the total amount of calories for the total amount of things you’re using.
When opened in excel, I get a #VALUE error. I’ve tried changing the syntax around a bit, to stuff like this
=SUMPRODUCT($D$53:$D$56, VLOOKUP($B$53:$B$56,$'Data '.$A$2:$'Data '.$I$100,5,FALSE))
but absolutely nothing changes. I’ve tried doing just a regular single vlookup to make sure that it’s actually able to find the data, and it does that no problem. The rest of the formula work fine. This happens whether I open it up as ODS, XLS, whatever.
Interestingly if I use an online converter from ods to xls, and open that - it works fine. Until I remove protected view and then it breaks. I’m not sure what this means, whether it’s just the last protected state of the file and the original data value are preserved or something. I hope this makes some sense and I’m happy to try and clarify anything.