Who here is good at MS Excel?

[viperlasson]

This question also needs experience in coding too. I normally don't ask questions about homework/assignments but I am stuck on this. What we need to do is replicate his grading program in excel. Need to output grade letters off of percentages (that was easy). But this one stumped me, out of 19 assignments, we need to make an IF statement that will output "Do not keep" on the two lowest scored assignments, and "keep" on the others. I bolded two because that is what is messing me up, I made my IF statement correctly say "Do not keep" for the lowest of them, but I don't know how to add in there to say a 2nd lowest one.

Here's my if statement sample, calculating numbers in A1 thru A4, and saying whether to keep or not keep the A1 assignment grade.

=IF(A1<A2, IF(A1<A3, IF(A1<A4, "don’t keep", "keep"),"keep"),"keep")

Head exploding

[Elesirdur]

Are you allowed to use VB macros in this assignment, or can you only put the algorithims in the Excel cell's formula entry?

It seems to me that this task would be far, far easier to accomplish with a VisualBasic macro rather than a clunky formula.

[Amele]

are you allowed to use functions besides IF?


I think I can do this but it's messy as hell to use just IF's and might bump up against the maximum number of nested functions.

the other issue that crops up is when you have more than 2 of the same minimum value.

(say) 5 grades, 10, 9, 5, 5, 5,

unless you're allowed to mark all three 5's?

[Edrick]

This might help keep things less confusing in the formula without insane nested IFs.

Example:

[B1]=IF(((A1<A2)*(A1<A3)*(A1<A4)),"don't keep","keep")

Checking A1 against 2, 3, and 4. Repeat through 19, and it'll be easy to get a single "Keep" "Don't Keep". The * symbolizes "and", which should help keep things simpler.

[Amele]

that's just Min() though; and also insufficient for the assignment (he has to identify and throw away the 'least two'; not the least one.)

so what you really need to do is identify any min (but just one) and then find any min (but just one) from the remaining set.

it's easy with script; it's tougher with just formula - it's insane with just "IF".

[souleman]

You have to actually mark them with the words "Keep" and "Do not Keep"?
Do you write that in the cell below them or what?

Doing the actual math is the easy part.

=SUM(A1:A19)-((SMALL(A1:A19,1)+SMALL(A1:A19,2))

That will add up all 19 numbers, then subtract the lowest 2. Obviously if that was in cell A20, you would then do =A20/17 to get the actual average.


SMALL(range,x)
Determines the x'th lowest number in range. So if x is 1, it gives the lowest number. If x is 5, it gives the 5th lowest number.




note: been a while since I have done this using array formulas, but I believe this should work also:
=AVERAGE(LARGE(A1:A19,ROW(1:17)))
That will give the avearge of the 17 largest numbers from A1 to A19.

[viperlasson]

Ohh I did not know about this SMALL function. He made this assignment so damn complicated. This is what my sample looks like, see how it says not to keep A1 since its the lowest number.

http://img251.imageshack.us/img251/8519/88935254je5.jpg

edit: Alright so =SMALL(A1:A4,1) and =SMALL(A1:A4, 2) will give me the two lowest numbers, that is a great leap. But I need it to output the phrase "do not keep" next to the lowest number and second lowest. So close >.<

[Amele]

=IF(
AND(
OR(
A1=SMALL(A1:D1,1),
A1=SMALL(A1:D1,2)),
SMALL(A1:D1,1)!=SMALL(A1:D1,2)),
DO NOT KEEP,
KEEP)

should do it more or less.

you need to replace 'keep' with an IF to handle the case of the smallest two values being the same value (as written, all I did was exclude the the system from accidentally marking the third lowest value if there's a duplicate of that value in the lower two)

[viperlasson]

Good god, so it uses if, and, and or statements? I'm posting in between classes right now so I can't test it out on my desktop computer, but damn thanks alot.

[souleman]

Tell your teacher he is a moron and use conditional formatting to make the 2 lowest numbers come up with a red background so he knows not to add them :/

Amele's code looks right to me also (though range is A1:A4 in example, not A1:D1). Might want to use Absolute refrence on the range to check.

=IF(
AND(
OR(
A1=SMALL($A$1:$A$4,1),
A1=SMALL($A$1:$A$4,2)),
SMALL($A$1:$A$4,1)!=SMALL($A$1:$A$4,2)),
DO NOT KEEP,
KEEP)


That way you can just use the autofill so you don't have to rewrte the formula for each cell from B1 to B4 (or whatever).

[Amele]

ah yeah, I intentionally did the range wrong since the actual assignment is 19 values, and not 4.

you're right about using absolute references though, slipped my mind when I typed it here.

you may want to double check != too, I am not 100% that that's the valid symbol in excel (I still want to write excel using || and && from too much time spent in other markup languages)

[souleman]

you may want to double check != too, I am not 100% that that's the valid symbol in excel (I still want to write excel using || and && from too much time spent in other markup languages)

I know what you mean... And I didn't even thing about that, probably actually have to do:

=IF(
AND(
OR(
A1=SMALL($A$1:$A$4,1),
A1=SMALL($A$1:$A$4,2)),
NOT(SMALL($A$1:$A$4,1)=SMALL($A$1:$A$4,2))),
DO NOT KEEP,
KEEP)

[Makenshin]

If you want, I would take the values and sort them from highest to lowest, and then just remove the last two elements linked list style, or just dont include the last two when you are adding them up.

[Amele]

If you want, I would take the values and sort them from highest to lowest, and then just remove the last two elements linked list style, or just dont include the last two when you are adding them up.

doesn't satisfy the requirements for the homework assignment.

[Makenshin]

oops. forgot about that part, all of my teachers don't make me do anything a certain way, as long as it works good and doesn't waste resources, it counts.

[viperlasson]

Finally got a working one, thanks for the help everyone :D

=IF(OR(A1=SMALL($A$1:$A$3,1), A1=SMALL($A$1:$A$3, 2)), "don’t keep", "keep")

works perfectly, just have to keep changing the A1= part to the corresponding cell. Learned the small function and was reminded about the OR function. Haven't done programming in a couple years, its in the back of my brain somewhere...

[Amele]

=IF(OR(A1=SMALL($A$1:$A$3,1), A1=SMALL($A$1:$A$3, 2)), "don’t keep", "keep")

you weren't required to solve for this case correctly?

{100, 80, 85, 95, 90, 85}

as written, your IF() would mark 80, 85, and 85 as don't keep.

(there's another case that's an issue: {80, 80, 80, 80, 80} but that's just being mean spirited.)

[souleman]

Also, if you use the "Fill Cell" command (you know that little black square in the bottom right corner of the cell), you don't have to change the A1. Just autofill, and it will autmatically change the A1 for you.

[viperlasson]

=IF(OR(A1=SMALL($A$1:$A$3,1), A1=SMALL($A$1:$A$3, 2)), "don’t keep", "keep")

you weren't required to solve for this case correctly?

{100, 80, 85, 95, 90, 85}

as written, your IF() would mark 80, 85, and 85 as don't keep.

(there's another case that's an issue: {80, 80, 80, 80, 80} but that's just being mean spirited.)

Yeah I overlooked that possibility when reading over the responses.

=IF(OR(OR(A1=SMALL($A$1:$A$9,1),A1=SMALL($A$1:$A$9 ,2)),NOT(SMALL($A$1:$A$9,1)=SMALL($A$1:$A$9,2))),
"don't keep","keep")

(used soulman's code above but made the AND an OR, and made my sample list bigger to A9)

works with the first scenario now but not with the 2nd one {80, 80, 80, 80, 80}. I hope he won't be that mean while he adds random numbers into the fields. I seriously doubt anyone in the class are even doing this, its a simple CIS101 class that I had to take and the people in this class are computer-tards.

[souleman]

Why did you make the and an or? That is most likely why its not working for the 2nd sample (80, 80, 80, 80, 80)