Comine logical AND and OR

[AdRock]

Hello

I am trying to write a function that checks to make usre that 2 adjacents cells (columns) are not empty. If they are just do nothing otherwise it then checks to see if there is a 'x' or 'X' in the next colum. Depending on the outcomre of that, it sums the first 2 cells (non blank).

I have made 2 separate functions. the first one checks to make sure the 2 cells aren't empty, the second checks to see if there is an 'x' in the cell and calculates the sum.

The cell which contains an x is much like a checkbox.

here are my 2 functions:

checks to see if 2 cells empty

=IF(AND(ISBLANK(E18),ISBLANK(F18)),"",SUM(E18*F18))

checks to see if 'checkbox' contains an 'x' and output the sum

=IF((OR(G2="x",G2="X")), SUM((E2*F2)/1.2),SUM((E2*F2))

what i need is to comine these 2 functions into 1 so as long as the first 2 columns contain numbers, it calculates 1 of 2 sums

Any ideas how to comine these?

 

[JamesW]

Hi there,

Welcome to the board.

From the top of my head, I think this may help:

=IF(AND(ISBLANK(E18),ISBLANK(F18)),"",IF((OR(G2="x",G2="X")),SUM((E2*F2)/1.2),SUM(E2*F2)))

(if e18 and f18 are blank do nothing, otherwise (e/f18 are not blank) if g2 = x or X then (e2*f2)/1.2 otherwise (g2 doesnt = x) e2*f2)

Not sure where you want the SUM(E18*F18). Can you tell me what needs to happen in order for this bit to happen?

 

[JackDanIce]

Welcome to the board.

What is the difference between

=E18*F18 
and
=SUM(E18*F18)
and
=SUM(E18:F18)

And I can't tell what you are testing to link the two formula.

E18 and F18 are in your first formuale; is this result being stored in cell E2 or F2? What cells link your first and second formulae?

Finally, instead of

OR(G12="X",G12="x")

You can use:

UPPER(G12)="X"

Which checks (forces) the upper value of G12, regardless of whether G12 is in lower or upper case

 

[AdRock]

Sorry

All the cells are on the same row. I had the first formula working and i was wrking an other sheet trying to find the solution

I think your formula works which i great. Many thanks

What it is for is an invoice template i'm working on. Some parts of the invoice don't have tax whereas others do so i needed to deduct the tax from taxable parts, add the tax on later so the totals should add up the same (haven't got that far yet)

Many thanks for your very quick response. I will be sure to come back here often as I use excel a lot at work

 

[lenze]

Hi and welcome to the board!!!
Can you give a little more detail??
1. If both cells in E:F are blank, what result do you want?
2. If 1 contains a number and the other is blank, what result do you want?
3. If both have numbers and G is an "X", what result do you want
4. If both have numbers and G is a "", what result do you want

lenze

 

[AdRock]

I just had to change the cell references around to one row and now it works

=IF(AND(ISBLANK(E2),ISBLANK(F2)),"",IF((OR(G2="x",G2="X")),SUM((E2*F2)/1.2),SUM(E2*F2)))

 

[JackDanIce]

I think a "cleaner"/shorter version would be:

=IF(AND(ISBLANK(E2),ISBLANK(F2)),"",IF(UPPER(G2)="X",E2*F2/1.2,E2*F2))

As I can't tell what the difference between SUM(A1*B1) and A1*B1 is when trying on a spreadsheet here...