IF function to multiply by different numbers

**t.jones** · 10-26-2011, 06:47 AM

Hi

I need to employ a potentially complicated IF function but not sure how to do it so would greatly appreciate some advice.

I have data in column A where the number in the first row is always between 0 and 4 and then the numbers down the column get slightly smaller with each row (down to 800 rows). I need a formula in column B that multiplies each row in column A by the same number (simple enough so far...) but the number to multiply by changes depending on what the value in cell A1 is, such that there are three categories - <0.1, 0.1-0.5 and >0.5, for example, a value of 0.03 in cell A1 would need to be multiplied by a different number than if the value in A1 was 0.6.

To further complicate things, there will be occasions where there is no data in column A and if that is the case I want to avoid any numbers being shown in column B, so the =IF('A1="", "",....) function will also have to be incoporated somehow.

Does anyone know how to do this?

Many thanks.

**Jamesr571** · 10-26-2011, 07:55 AM

It would seem you have to do If statements inside IF statements.

I have attached an example, i believe the formula i used will work for you but you may need to change some ranges.

Hope this helps

**alansidman** · 10-26-2011, 07:59 AM

Suggest you post all the criteria and conditions so that someone can give you a valid formula. Otherwise, it will be difficult to determine if this can be performed with a nested if statement or you need a choose statement or some VBA (Case Select).

Alan

**t.jones** · 10-26-2011, 08:58 AM

Jamesr571 - thanks so much, that's exactly what I'm after. Well, almost exactly, whatever number the first row is multiplied by is the number that all of the rest of the values in that column must be multiplied by, but I've done that by adding a few $ symbols.

Thanks so much for your help!