Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[Taxi_Driver]

Any expert excel users about??

Im trying to use a conditional formula......I have the first part of it working ....

which is .....

=IF(AND(B2="W",D2=1),(F1+C2+1))

now i want to add this to it.....

=IF(AND(B2="W",D2>1),(F1-1))

I am thinking i need to use the OR command and drop the =IF from the second statement......but no matter what i try, excel doesnt like it, and i cannot find any "help" in using the IF/OR/AND  arguments together.

I've two other statemets to add to it, but if i can find out/or be shown how to add the second statement, then i sure i can work the rest out....

TIA   

[Entwood]

Not sure what you are trying to do .. but multiple conditional statements can be joined by a *

to read

=IF(AND(B2="W",D2=1),(F1+C2+1))*AND(IF(AND(B2="W",D2>1),(F1-1)))

As I don't know the input values and the output required it's a tad hard to do more !!

another solution is

IF(AND(B2="W",D2=1),(F1+C2+1))OR(IF(AND(B2="W",D2>1),(F1-1)))

Which will give a totaly different solution !!

[Taxi_Driver]

THanks for that Mr E, I will take closer look later this after, gotta dash out now 

And let you know what im trying to achieve 

[Taxi_Driver]

Not sure what you are trying to do .. but multiple conditional statements can be joined by a *

to read

=IF(AND(B2="W",D2=1),(F1+C2+1))*AND(IF(AND(B2="W",D2>1),(F1-1)))

As I don't know the input values and the output required it's a tad hard to do more !!

another solution is

IF(AND(B2="W",D2=1),(F1+C2+1))OR(IF(AND(B2="W",D2>1),(F1-1)))

Which will give a totaly different solution !!

I thought this line would give the correct result, but i had to add an "=" to the first IF and add a "*" to OR to stop excel complaing, but it doesnt do what i expected 

Basically the first part before the OR says If B2=W and D2=1 then the result is to add F1/C2/1 and do nothing if thats not the case, after the OR if B2=W and D2 is greater than 1 then take 1 away from F1 and do nothing if thats not the case

So if F1 = 0
       C2 = 5

Then if B2=W and D2=1 then result would be 6
........if B2=W and D2=2 then the result would be -1

I was getting the answer 6 with just the first part of the formula, so correct......but now with the second part added im getting 0 

[Taxi_Driver]

Ok......decided that this was getting way too complicated 

As if i get these two formula to work together, ive another 2 to add........so i decided to keep it simple and easier to see if things go wrong, if i just work out the formula individually and just add the results .......... simples 

[zirk]

I find it sometimes easier to break the formulas up into other cells (hidden somewhere) then use the hidden cells for a lookup for the next equation.

I use to do lots of tricky stuff in Lotus 123 quiet easily, but know struggle with some on Excel, maybe Im getting old or Excel trying to get too clever.   

[Taxi_Driver]

I find it sometimes easier to break the formulas up into other cells (hidden somewhere) then use the hidden cells for a lookup for the next equation.

I use to do lots of tricky stuff in Lotus 123 quiet easily, but know struggle with some on Excel, maybe Im getting old or Excel trying to get too clever.

Exactly what i've done, tho not bothered to hide them, as I can see the results the cells are getting and to start with I can check in my grey cells if its correct 

And ive now got a working spreadsheet that 'appears' to be working correctly 

[CaptainZok]

If Cust = Pished then let Fare = Fare*2 + a sausage for Holly Dog. 

[Taxi_Driver]

If Cust = Pished then let Fare = Fare*2 + a sausage for Holly Dog. 

Not quite Mr Zok.....tho HollyDog did have a sausage at lunchtime today 

Long story....short.....My mum dropped a cooked sausage on the kitchen floor, HollyDog was straight in there to clean up the mess 

As for the spreadsheet.....well...horses for courses 

[CaptainZok]

Fare*3?
You know Holly the Hoover won't let a sausage lie for long Dave.

[Entwood]

Okies .. now I know what you are trying to do, I see the problem ... you are trying to do the second part D2=2 as a separate calculation .. when it needs to be nested as the "result" of the first IF failing

along the lines of if D2=1 (answer is),(IF d2=2 (answer is different)) .... 

I think you'll find this works ...

=IF(AND((B2="W"),(D2=1)),(F1+C2+1),(IF(AND((B2="W"),(D2>1)),(F1-1))))

so we have an IF(AND((item1test),(item2test)),(if bothtrueresult),(ifnottrueresult)) and you enter the second set of tests in the ifnottrueresult, so if items1 or item2 are FALSE then the second test IF(AND((item1testagain),(item2testagain)),(if bothtrueresult),(ifnottrueresult)) and then you can enter another set of tests in ifnottrueresult and do a third set of tests ... and you can do this up to 64 times !!!  although the line gets stupidly long !!! and very difficult to error check. Lookup table is easier

That reads really badly ... so I'll try it this way ...

IF (item1testtrue AND item2testtrue),trueresult,[false result is (IF(item1testagaintrue AND item2testagaintrue),truesresult2),([falseresult ......])]

given your figures we get

IF(AND [w,1] 0+5+1=6, (IF AND[w,2], 0-1=-1,))

HTH

[smithpa7]

Okies .. now I know what you are trying to do, I see the problem ... you are trying to do the second part D2=2 as a separate calculation .. when it needs to be nested as the "result" of the first IF failing

along the lines of if D2=1 (answer is),(IF d2=2 (answer is different)) .... 

I think you'll find this works ...

=IF(AND((B2="W"),(D2=1)),(F1+C2+1),(IF(AND((B2="W"),(D2>1)),(F1-1))))
so we have an IF(AND((item1test),(item2test)),(if bothtrueresult),(ifnottrueresult)) and you enter the second set of tests in the ifnottrueresult, so if items1 or item2 are FALSE then the second test IF(AND((item1testagain),(item2testagain)),(if bothtrueresult),(ifnottrueresult)) and then you can enter another set of tests in ifnottrueresult and do a third set of tests ... and you can do this up to 64 times !!!  although the line gets stupidly long !!! and very difficult to error check. Lookup table is easier

That reads really badly ... so I'll try it this way ...

IF (item1testtrue AND item2testtrue),trueresult,[false result is (IF(item1testagaintrue AND item2testagaintrue),truesresult2),([falseresult ......])]

given your figures we get

IF(AND [w,1] 0+5+1=6, (IF AND[w,2], 0-1=-1,))

HTH

I thought I had a broad understanding of Excel. You have just confirmed I haven't a clue....

[Entwood]

It has very little to do with Excel per se, that is just a spreadsheet programme !!

What it has to do with is the use of logical embedded expressions, which I learned a long time ago when doing my degree in mathematics and computing !!

The one thing I don't like about Excel is its LACK of logical expressions ... that line would be far easier to write using

IF x OR y THEN c OR d ELSE IF ...   but Excel does not allow THEN or ELSE let alone ELSE IF

[Taxi_Driver]

Okies .. now I know what you are trying to do, I see the problem ... you are trying to do the second part D2=2 as a separate calculation .. when it needs to be nested as the "result" of the first IF failing

along the lines of if D2=1 (answer is),(IF d2=2 (answer is different)) .... 

I think you'll find this works ...

=IF(AND((B2="W"),(D2=1)),(F1+C2+1),(IF(AND((B2="W"),(D2>1)),(F1-1))))

so we have an IF(AND((item1test),(item2test)),(if bothtrueresult),(ifnottrueresult)) and you enter the second set of tests in the ifnottrueresult, so if items1 or item2 are FALSE then the second test IF(AND((item1testagain),(item2testagain)),(if bothtrueresult),(ifnottrueresult)) and then you can enter another set of tests in ifnottrueresult and do a third set of tests ... and you can do this up to 64 times !!!  although the line gets stupidly long !!! and very difficult to error check. Lookup table is easier

That reads really badly ... so I'll try it this way ...

IF (item1testtrue AND item2testtrue),trueresult,[false result is (IF(item1testagaintrue AND item2testagaintrue),truesresult2),([falseresult ......])]

given your figures we get

IF(AND [w,1] 0+5+1=6, (IF AND[w,2], 0-1=-1,))

HTH

Thankyou for your help Mr E....

I actually needed to add another 4 calculations to the string......so as you say it will get silly long and difficult to find errors.

So Ive left it as 6 seperate calculations outputing to 6 differents cells. This way there should be only one cell with a result in it (well nearly, ive set the formula to output 0 if false).....so dead easy to see if theres an error on my part with the equations. I just then sum the 6 output cells and dump the result where i wanted it in the spreadsheet