Results from formula or another cell

Microsoft Office 2007 home and student
June 22, 2010 at 11:36:08
Specs: Windows Vista
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. :-)


See More: Results from formula or another cell

Report •

#1
June 22, 2010 at 12:51:45
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...



Report •

#2
June 22, 2010 at 13:51:31
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. :-)


Report •
Related Solutions


Ask Question