I am working on a spreadsheet to that has a list of items, and each item has its on weight (for example) and multiple possible options that that may or may not be present, in any combination, that would alter the weight of the final combination of item and options.
I have tried this both with and without out the use of the SUM formula, and in the current iteration of:
=$A4*SUM(
VLOOKUP($B$4, RT.$A$5:$AH$999, G$1, 0)
;IF($C4=“Y”, VLOOKUP($C$3, RT.$A$5:$AH$305, G$1, 0), 0)
;IF($D4=“Y”, VLOOKUP($D$3, RT.$A$5:$AH$305, G$1, 0), 0)
;IF(E$4=“Y”, VLOOKUP($E$3, RT.$A$5:$AH$305, G$1, 0), 0)
;IF(F$4=“Y”, VLOOKUP($F$3, RT.$A$5:$AH$305, G$1, 0), 0)
)
Item Count * VLOOKUP (Item Name, Range, Position) + If(Option One=“Yes”, VLOOKUP(Option Name, Range, Postion), Else 0) + If(Option Two=“Yes”, VLOOKUP(Option Name, Range, Postion), Else 0) + etc
Unfortunately, the results from the formula are intermittent.
I am able to have the “weight” value of the item added to the “weight” value of the options, but as soon as I move on to the next piece of data “length” (for example), it sums to correct value -1, and on the third piece of data “height” (for example) it ignores the additional value entirely. On the fourth piece of data “depth” (for example) it sums correctly.
The intermittent failure of LibreOffice to correctly SUM the results of the nested functions is extremely frustrating.
Other than the standard admonishments regarding negative numbers, decimals, and making sure the cells are set up for integers instead of text, what is the major malfunction?
How do I coax LibreOffice to handle this formula properly, preferable without having to start from scratch with a new database? I am attempting to create a concise spreadsheet with limited possibilities (using the Data Validity function), to quickly select items and options, and have it tally the resulting specifications from there without the end user having to do all of the research and calculations themselves.
Thanks in advance for any advice that can be offered.