Microsoft Office 2007 home and student

I have entered the following formula into a cell: =IF(VLOOKUP(C9,'OTTA YEAR END 2010.xlsx'!ContractFunding,10,)="a", B9/N9*O9-P9, "")

It works (yay!). But I need it to do more.

This formula is located in Cell Q9.

If the answer to this formula is greater than the amount shown in cell J9 then I want it to equal what is in J9.

If the answer to this formula is less than or equal to the amount shown in cell J9, then I want it to show the result of the formula.

Thanks for any help you can give. :-)

We need to be careful here. When you say "

If the answer to this formula is greater than J9", which part of the formula are you referring to?The reason I ask is because of this:

1 - If the VLOOKUP does not find the "a", the result of that entire formula is going to be "".

2 - Excel will consider "" to be greater than any numeric value found in J9. It's based on how Excel handles text strings.

So the question is: Does "" count as a value to be checked against J9 or do you only want to check the result of B9/N9*O9-P9 against J9?

Assuming the latter, try this:

=IF(VLOOKUP(C9,'OTTA YEAR END 2010.xlsx'!ContractFunding,10,)="a", IF(B9/N9*O9-P9>J9 ,J9, B9/N9*O9-P9), "")

This should still return "" if the "a" isn't found, but will compare B9/N9*O9-P9 against J9 if the "a" is found.

Let us know...

You assumed correctly. :-) When I said "If the formula is greater than J9" I was referring to: B9/N9*O9-P9

It looks like that was it! I'll have to run a few tries with it, but I think this is what I needed.

Thank you so much for all of your help. :-)

Ask Your Question

Weekly Poll