Excel not being logical

[High Sierra]

=IF(L2=90,10,"")&IF(L2=130,20,"")&IF(L2=100,10,"") &IF(L2=105,20,"")&IF(L2=110,10,""&IF(D2="SWING*",0 ,""))


the last argument should mean that if the word swing is input in cell D2, then it deducts 10 from the final column.

it doesnt and I don't know why.

[Equium Duo]

why have you got a '*' after SWING? is that intended? Going on how you describe it, then the last part should read ".....D2="SWING",0,""))

or am I misinterpreting what you are saying?

You've also got spaces after '&' some of the time between the IF functions, but not all fo the time. Which way should it be? " &IF..." or "&IF..."

[High Sierra]

hmm i dont know why that is - theres no spaces in the spreadsheet
also i noticed im missing a bracket on the last argument - changed it and it makes no difference, excel still does not do anything when 'swing' is typed in cell D2

[Ralara]

why have you got a '*' after SWING? is that intended? Going on how you describe it, then the last part should read ".....D2="SWING",0,""))

or am I misinterpreting what you are saying?

You've also got spaces after '&' some of the time between the IF functions, but not all fo the time. Which way should it be? " &IF..." or "&IF..."

* is wildcard.

I'd have put *SWING* myself.

But then I don't "do" excel.

HS where's the thingy that minuses 10?

[NoirAvlaa]

Why don't you just do like, final cell "=total+D2" and then instead of writing "SWING" and a massive fail formula just write "-10". Or whatever. Seems like you're making it well more complicated than you need to.

[shaewyn]

=IF(L2=90,10,"")&IF(L2=130,20,"")&IF(L2=100,10,"") &IF(L2=105,20,"")&IF(L2=110,10,"")&IF(D2="SWING*",0,"")


the last argument should mean that if the word swing is input in cell D2, then it deducts 10 from the final column.

it doesnt and I don't know why.

missing close bracket in the *second last* if statement. (fixed in the quote)

also, this seems a curious way to go about this, instead of nesting them as "else" options. Should work, however.

I'd have gone:
=IF(L2=90,10,IF(L2=130,20,IF(L2=100,10,IF(L2=105,2 0,IF(L2=110,10,IF(D2="SWING",0,"")))))

[High Sierra]

ok this is a formula for a payroll sheet.

what I want it to do is this:

add in or subtract different rates of pay according to what has been done.

so if the word 'swing' is added in to cell D2 (and only swing, nothing else) then it DOES NOT add 10 to the final column i.e. IF 'Swing' in D2 then add 0 to final column


hmmm

think I might need to upload to google docs to show what I mean

[High Sierra]

thats a dumb explanation.

the formula represents different rates of pay for different jobs.
however to represent the job titled 'swing' the cell that this formula is in needs to show 0 as it usually shows 10 or 20 (depending on the rate of pay) which you can see in the first and second argument.

ie. (L2=90,10,"") and (L"=130,20,"")
where the second figure is a bonus

so we can quickly have the different rates of pay input into the spreadsheet and it calculates the right rate of pay immediately with no further input. however ifthe job 'swing' is done, then no bonus is payable, therefore in this specific instance it should not add in the bonus column at all which is where this formula sits

[kzig]

I would suggest using vlookup to handle all the possible values from L2, then conditionally replace with 0 using an IF based on D2.

L2	value
90	10
130	20
100	10
105	20
110	10

Then you get something like

=vlookup(L2, lookuptable, 2, 0) * if(D2 = "SWING",0,1)

[High Sierra]

I would suggest using vlookup to handle all the possible values from L2, then conditionally replace with 0 using an IF based on D2.

L2	value
90	10
130	20
100	10
105	20
110	10

Then you get something like

=vlookup(L2, lookuptable, 2, 0) * if(D2 = "SWING",0,1)

thanks I'll try that today and see if it does