Help With List and functions

[rkm140]

let say in cell A6 i have a list

SnG
Cash
MTT

I can pick one of these. If i pick Cash then i want a specific cell to run a specific formula. depending on the item chosen.

So in cell A6 is the list.

in cell B6 an amount will be place and in C6 another amount will be place. the number for c6 will range from a -number to a +number.

But D6 looks to see if B6 meet the requirements for MTT, SnG or Cash depending on which item was selected from the list. and uses the formula below with $c$3 changing from to either $D$3 or $F$3 and comparing the values for the chosen item. the formula changes would look this


=IF(ISBLANK(C6),IF(B6>$C$3,"Do Not Play","PLAY"),IF(C6<B6, "Lost","Win"))

=IF(ISBLANK(C6),IF(B6>$D$3,"Do Not Play","PLAY"),IF(C6<B6, "Lost","Win"))

=IF(ISBLANK(C6),IF(B6>$F$3,"Do Not Play","PLAY"),IF(C6<B6, "Lost","Win"))

[NBVC]

Perhaps?

=IF(ISBLANK(C6),IF(OR(AND(A6="SnG",B6>$C$3),AND(A6="Cash",B6>$D$3),AND(A6="MTT",B6>$F$3)),"Do Not Play","PLAY"),IF(C6<B6, "Lost","Win"))

[rkm140]

Slightly Modified. but still doesn't work.

when A20 is blank the F20 should be blanks also. if a value in C20 is less than the values in "SNG" "MTT" "cash" and no value has been entered in D20 then it will look at the values in B14 (SnG) B16 (cash) and B15 (MTT) depending on which was selected in A20 from the drop down list.

As is the formula report play when nothing was entered, and if a value is entered in c20 it reports win even if no value was placed in d20. it should look back and check against the values stated above until a value has been placed in D20

Am i making sense

=IF(ISBLANK(A20),IF(OR(AND(A20="SnG",C20>$B$14),AND(A20="Cash",C20>$B$16),AND(A20="MTT",C20>$B$15)),"Do Not Play","PLAY"),IF(C20>D20, "Lost","Win"))

[rkm140]

anyone know how i can fix this.

[NBVC]

Please allow some time....

[rkm140]

Thanks Will do sir.

[NBVC]

Is this what you are looking for?

Please Login or Register  to view this content.

[rkm140]

Ok, almost there i think. if it is blank then the cell remains blank. but if a value is put into C20 it jumps to the end of the formula and reports lost even if no value has been placed in D20.

If no value has been place in D20 it should look back at the values in SNG, MTT or Cash for the one selected and compare if the value in c6 is greater the then value it is compared to and report Do Not Play, if it is less than said value it should report Play.

Once a value is returned in D20 it should look at D20 and C20 and compare if the value in D20 is greater than the value in C20 it reports Win.

As is it is reporting the back end of the formula without comparing any of the other condition.


=IF(A20="","",IF(AND(D20="",C20<MIN($B$14:$B$16)),IF(OR(AND(A20="SnG",C20>$B$14),AND(A20="Cash",C20>$B$16),AND(A20="MTT",C20>$B$15)),"Do Not Play","PLAY"),IF(C20>D20,"Lost","Win")))

[NBVC]

Do you have anything in B14:B16?... those need to be filled.

[rkm140]

yes b14:b16 looks back at another cell and does some math. and reports a value depending on the the value in to other cell.

I have added the sheet so you can look it over and have a better idea of what is going on.

[NBVC]

Okay, maybe then?

=IF(A20="","",IF(D20="",IF(SUMIF($A$14:$A$16,A20,$B$14:$B$16)>C20,"Do Not Play","PLAY"),IF(C20<D20,"Lost","Win")))

[rkm140]

Closer. Now if i enter a value in C20 and there is no value in D20 it reports Play, but it isn't not comparing the values.

The cell remains blank if nothing is selected in A20. but when this is selected and a value is put in C20 it isn't comparing the value for the correct value for "SNG" "MTT" "Cash" and comparing.

It is looking at the sum and as long as the value in C20 is less than the sum it reports play.

again when C20 has a value of say 6, and SnG has been selected in A20. the formula will look back and see the value in B14 and if B14 is greater then C20 reprots "Do Not Play". This needs to look at A20 and see what was selected and look at the corresponding value for what was selected.

[NBVC]

Closer. Now if i enter a value in C20 and there is no value in D20 it reports Play, but it isn't not comparing the values.

The cell remains blank if nothing is selected in A20. but when this is selected and a value is put in C20 it isn't comparing the value for the correct value for "SNG" "MTT" "Cash" and comparing.

It is looking at the sum and as long as the value in C20 is less than the sum it reports play.

again when C20 has a value of say 6, and SnG has been selected in A20. the formula will look back and see the value in B14 and if B14 is greater then C20 reprots "Do Not Play". This needs to look at A20 and see what was selected and look at the corresponding value for what was selected.

It is not taking the Sum... Sumif() here will return the matching number to the value in A20... because there will only be one match, it picks the one number...

Also, 6 is larger than 2.50 that you have assigned to SnG... so it is reporting back "Play".... If you put 1.50 in C20, it reports "Do Not Play" which looks like per your instructions...