[Solved] Formula Separation Assistance

[bfpa40]

I am creating a spreadsheet using calc to record my vblood sugar levels and based upon what they are how much insulin i have to take. I am trying to use a cell to determine what the value of another cell will be when that number falls between different ranges. So ive been able to get a formula that compares the number to a range of numbers now I need to add to it so it compares different ranges of numbers and gives a value to populate a cell. Here is what I have that isnt working i need a way to seperate the different =IF statements within the formula. Any help would be greatly appreciated. What i have that isnt working: =IF(AND(C1>179,C1<230), "24"), =IF(AND(C1>229,C1<280), "26"), =IF(AND(C1>279,C1<330), "26")

-Brian-

[FJCC]

I think you need to use the VLOOKUP function. Look at the result in A1 as the value in C1 changes. I had to guess at the values required for some conditions.

[John_Ha]

When building spreadsheets it is often useful to use helper columns (or helper rows).

You use the helper columns so you can break everything down into very small steps to see if that step is correct. When you get all the individual small steps correct you can either

• hide all the helper columns (rows), leaving just the result; or
• merge the formulae you have written in adjacent helper columns (rows) until you have just one formula

If you decide to merge, you have the advantage that you have the correct answer available to you and, if a merge gives the wrong answer, you know immediately that merge has gone wrong.

Complex IF statements are notoriously difficult to get correct or debug and VLOOKUP and HLOOKUP are much better. See Chapter 8 - Using the DataPilot in the Calc guide which starts:

Many requests for software support are the result of using complicated formulas and solutions to solve simple day to day problems. More efficient and effective solutions use the DataPilot, a tool for combining, comparing, and analyzing large amounts of data easily. Using the DataPilot, you can view different summaries of the source data, display the details of areas of interest, and create reports, whether you are a beginner, an intermediate or advanced user.

See Easy way to make highly nested conditionals which uses helper rows.

See Calc Functions listed by category - it has worked examples.

Don't forget Tools > Detective > Trace precedents / descendents ..., as an aid to debugging. And you can add redundant spaces and brackets in formulae which helps make the various arguments much clearer.

[bfpa40]

All,
While I know enough to get me in trouble this is proving more difficult than expected. I will try to research VLOOKUP and Helper Columns and see if I can figure more out. Please keep the suggestions coming. Thank You

-Brian-