Excel Help - Conditional Formula

Microsoft Excel 2007
January 14, 2010 at 14:00:12
Specs: Windows XP
I am not sure if this would be formatting or not but what I want to do is to create a condition which would look at a number in cell A1 and if cell A2 is greater than 20% of that number do the calculations in row 2. But if cell A2 is less than 20% of A1 then do not continue with the calculations but rather return the values in row 1. Each row has about 10 different calculations, so I need a conditional statement that would either continue with the calculations or stop them and return the data in the row above.

See More: Excel Help - Conditional Formula

Report •

January 14, 2010 at 15:48:54

If your two values for the 20% comparison are in cells A1 and A2
and cells B1, C1 and D1 contain the base values
and cells B2, C2 and D2 contain data for a calculation, for this example I entered a percentage value in these cells
There are formulas in cells B3, C3 and D3 which respond to the difference between the values in A1 and A2.
If the difference is less than 20%, the values in B1, C1 and D1 appear in row 3,
but if the difference is 20% or more then a calculation is performed. For this example it was the base values in row 1 plus the percentage in row 2

Here are two sets of results:

	A	B	C	D
1	50	13	15	17
2	59	10%	10%	10%
3		13	15	17

	A	B	C	D
1	50	13	15	17
2	60	10%	10%	10%
3		14.3	16.5	18.7

Enter this formula in Cell B3


Note the $ signs.
Extend the formula by dragging it through columns C and D

Hope this is what you were looking for.

BTW if you make an error in your post, you can edit it rather than re-posting - there is an edit link in the top right corner of your own post.


Report •

January 14, 2010 at 17:17:17

Please try to use a subject line that give the readers some idea of what you are asking about.

If everyone used a subject line like "Excel Help" we wouldn't be able to tell one thread from another.

I took the liberty of changing your subject line to be more meaningful.

In addition, I see that you posted your question twice. Please refrain from doing that in the future.

I have deleted your other post.

Office Forum Moderator

Report •

January 15, 2010 at 07:06:12
I will try that. What I wanted to see was the 1st example but only rows 1 and 3. I assume I just need to put the percentage calculation in front of my formula in the 2nd column which is =IF(ISERROR(VLOOKUP(B:B,'2006 Components '!$A$5:$Z$125,24,FALSE)),"0",VLOOKUP(B:B,'2006 Components '!$A$5:$Z$125,24,FALSE)) and then refer back up the cell above if the condition is met. I also assume that I would put the same type percent calculation in front of the other cell in the row where I do not want the calculation to occur if the same conditon wasn't met. Are my assumption correct?

Report •

Related Solutions

January 15, 2010 at 07:57:57

I am not quite sure what you are trying to do with your VLOOKUP() formula. I don't think you can use a whole column B:B as the value to be searched for. Testing B:B in VLOOKUP, B:B returns 0 (zero) whatever is in column B, and VLOOKUP searches for zero in the range on the '2006 Components ' page.

You are right about including the calculation in the formula. There is no need to refer to a cell in row 2, just hard code the values into the formula.

The following formula tests the difference between A1 and A2 for a 20% difference. If true (20% or greater difference) it uses the formula you posted, by looking up the value of B1 in the range '2006 Components '!$A$5:$A$125, (lookup is in first column of range). I changed B:B in your formula to B1 to make this work). If it finds a match it returns the value in the same row but in column X (you used 24 in your formula).
If False (less than 20% difference)it just returns the value in B1.

=IF($A$2-$A$1>=$A$1/100*20,IF(ISERROR(VLOOKUP(B1,'2006 Components '!$A$5:$Z$125,25,FALSE)),"0",VLOOKUP(B1,'2006 Components '!$A$5:$Z$125,24,FALSE)),B1)

There is one other possible result and that occurs when the difference is 20% or greater, but the value in B1 is not present in the range '2006 Components '!$A$5:$A$125 and the If(IsError returns 0. Note that you put 0 in double quotes "0" which is text rather than a number.

If you drag the formula one cell to the right it will refer to cell C1 but still test A1 & A2 for the 20% difference.

Hope this helps.


Report •

Ask Question