Excel help

Hedgecutter

Hi guys, doing and excel course and I'm stuck on option 2 and 3. any help would be appreciated.

Excel attached

cheers

the_pen_turner

are you allowed to add more columns or can it only be a formula

Hedgecutter

the_pen_turner wrote: »

are you allowed to add more columns or can it only be a formula

I miss read the question, I only need one of the three options. is it possible to do all in one column ?

i think you are supposed to have them all in one formula, it's not 3 separate things.
change your 'no voucher' to add in another if statement in the 'false' clause.

if(4 years and 1000 spend, 50 euro voucher, (if(6 months and 400 spend then 25 voucher, 5 voucher)))

Hedgecutter

bluewolf wrote: »

i think you are supposed to have them all in one formula, it's not 3 separate things.
change your 'no voucher' to add in another if statement in the 'false' clause.

if(4 years and 1000 spend, 50 euro voucher, (if(6 months and 400 spend then 25 voucher, 5 voucher)))

Is the IF(AND correct?

Hedgecutter

Hedgecutter wrote: »

Is the IF(AND correct?

does it work for the formula you have in there already? what happens if you change cell c3 to 500, do you get 'no voucher'?



(yes, it is, but you should verify that yourself)

Tenigate

Hedgecutter wrote: »

I miss read the question, I only need one of the three options. is it possible to do all in one column ?

It says to use a nested if.. which means all 1 column.
If(firstcondition=true,"first condition is true","first condition is false")

A nested if would be..
If(firstcondition=true,"first condition is true",if(secondcondition=true,"second condition is true","second condition is false, and so is the first"))

Edit:apologies, nested if.. but ANDs and determining if conditions are true/false is best done with columns. Still, can all be done in one cell

the_pen_turner

it is possible but a pain , I would never do anything like this in one formula. too much risk and complication.
take baby steps is my way. there is plenty of capacity in excel. you wont fill it up.



option 2 is the same as example just change the values to less than 0.5 and 400.

option 3 is the same only change no voucher to 5 euro voucher

I will think about how to do all thre together . which is what I think you need

daphne

Yes he's right. Something like:

=If(and(b1>500,c1>2), "50euro voucher", if(and(b1>100, c1>0.5), "25 euro voucher", "5 euro voucher"))

On phone so the numbers and text may not be right.

Hedgecutter

Thanks Guys working now. Bit of a head wreck.