We help IT Professionals succeed at work.

How do I combine these two formulas in one cell in Excel 2007 to get them to work?

106 Views
Last Modified: 2017-04-13
The first formula:

=IF(ISNUMBER(SEARCH("*auto*";P33));Q33+Q37;Q34+Q37)
(If "auto" and possibly other text in the same cell is found, then Q33+Q37; otherwise Q34+Q37.
This formula works fine on its own.

The second formula:

=(1-(VLOOKUP(K4;Travbanor!BD104:BE153;2;FALSE)))/(VLOOKUP(K4;Travbanor!BD104:BE153;2;FALSE))+1
(First find the value and substract this from 1, then divide this result with the same found value again, then finally add 1.)

When combining the two formulas, I need this:

1. No value error or similar should be printed (empty target cell if can't calculate). In other words, IFERROR needs to be added.

2. The result of the first formula should be multiplied with the result of the second formula.

An example of real values:

Q33 contains 12.6, Q34 contains 11.9, P33 contains "auto" (so use value in Q33), Q37 contains -0.3, K4 contains 1640 which is the lookup value in Travbanor! and which returns 0.914.

The result then would be this for formula one: 12.6-0.3=12.3
The result then would be this (or should be if the formula is correct) for formula two: 1-0.914=0.086, 0.086/0.914=0.094, 0.094+1=1.094.
The final result when the two formulas are combined would be 12.3*1.094=13.5.
Comment
Watch Question

Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
Try this:

=IFERROR(IF(ISNUMBER(SEARCH("*auto*";P33));Q33+Q37;Q34+Q37)*(1-(VLOOKUP(K4;Travbanor!BD104:BE153;2;FALSE)))/(VLOOKUP(K4;Travbanor!BD104:BE153;2;FALSE))+1,"")

The double quotes at the end will give blank cell if an error occurs.

Thanks
Rob

Author

Commented:
Rob,

I tried your formula now, but I get this final result after I have pasted your formula in the cell: 2.11524
Finance Analyst
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks very much Rob, your formula worked fine now. I get the correct result calculated when the cell above is populated, and nothing displayed if the cell above is empty.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.