Search code examples
excelexcel-formulaworksheet-function

If cell H5 contains X return value from H5, else return value from cell ##

I am stumped on something i thought was very simple to acheive;

If the cell contains a number or better still starts with lets say letter "P", then return the value from that cell, if not return a different argument.

I am trying to override a calculated figure, if a manual figure is entered; The manual figure will ALWAYS take precedent over the calculated figure.

I have tried the following scenarios, all of which do not work entirely;

=IF(LEFT(H5,1) = "£", H5, SUM(AO5:AU5))

I appreciate that it is only looking for the £ sign, i have tried various combinations

=IF(ISNUMBER(SEARCH(,H7,1)),H7,SUM(AO7:AU7))

This works, but returns anything entered in cell H7 not just if it contains a number

=IF(H8>=1,H8,SUM(AO8:AU8))

This works, but returns anything entered in cell H8 not just if it contains a number

Again i appreciate why they return anything, i just don't know how to write the argument correctly.

Many thanks

Solution

  • I am unsure what the exact problem is, but building on your formulas, here are some tests and their results:

    Formulas

    Seems to me that all formulas work as expected. If this is not the case for you, you have to describe (1) the expected result and (2) what result you are actually getting.