Hi guys,
What I'm trying to do is too complex for a regular Excel formula, so I need a VBA. Unfortunately, I know very little about VBA, so I'm really counting on this forum. I think this might be a challenging VBA, but hopefully fun too!
As it is now, column CH has a formula that takes the sum of the top three and bottom three values in columns BV:CG. The criteria for "top" and "bottom" is based on the ranks in AX:BH--i.e. if a cell's corresponding "rank" is in the top three highest or lowest in all of the ranks of that row, then that cell is added to the sums in CH. *The formula also only runs if the sums of all corresponding ranks on that row add up to 45* Formula=(IF(SUM($AX4:$BH4)>=45,SUM(SUMIF($AX4:$BH4,LARGE($AX4:$BH4,{1,2,3}),$BV4:$CF4))+SUM(SUMIF($AX4:$BH4,SMALL($AX4:$BH4,{1,2,3}),$BV4:$CF4)),FALSE))
As it is now, it's wrong.
First: Instead of using different ranks each day, I need to use the same criteria for the whole work week (5days). In other words, I need to target these parts of the above formula "SUMIF($AX4:$BH4,LARGE($AX4:$BH4,{1,2,3}),$BV4:$CF4)" and "SUMIF($AX4:$BH4,SMALL($AX4:$BH4,{1,2,3}),$BV4:$CF4)" and change them so that the large{1,2,3} and small {1,2,3} of $AX4:$BH4 hold for BV4:CF8, i.e. the whole week.
Second: The criteria on the last day of the proceeding loop is the criteria for the first day of the next loop. In other words, thought about in a real life scenario, one can only make a decision for that week based on the criteria of the day before.
Third: If the loop hits a "FALSE" statement (which would happen if the sum of the ranks is less than 45), I need excel to take the next available criteria, and apply that to the next 5 days.
Fourth: This (above statement) means that the loop needs to be linked to a calender. I can't just apply the same criteria to 5 cells, the next criteria to the next 5 cells, ect, because on the FALSE days, there would not be a sum, but instead the sum would pick back up at the next available criteria, and THEN apply that to the next 5 cells.
Even a partial code or input would be helpful. I'm really running on nothing.
What I'm trying to do is too complex for a regular Excel formula, so I need a VBA. Unfortunately, I know very little about VBA, so I'm really counting on this forum. I think this might be a challenging VBA, but hopefully fun too!
As it is now, column CH has a formula that takes the sum of the top three and bottom three values in columns BV:CG. The criteria for "top" and "bottom" is based on the ranks in AX:BH--i.e. if a cell's corresponding "rank" is in the top three highest or lowest in all of the ranks of that row, then that cell is added to the sums in CH. *The formula also only runs if the sums of all corresponding ranks on that row add up to 45* Formula=(IF(SUM($AX4:$BH4)>=45,SUM(SUMIF($AX4:$BH4,LARGE($AX4:$BH4,{1,2,3}),$BV4:$CF4))+SUM(SUMIF($AX4:$BH4,SMALL($AX4:$BH4,{1,2,3}),$BV4:$CF4)),FALSE))
As it is now, it's wrong.
First: Instead of using different ranks each day, I need to use the same criteria for the whole work week (5days). In other words, I need to target these parts of the above formula "SUMIF($AX4:$BH4,LARGE($AX4:$BH4,{1,2,3}),$BV4:$CF4)" and "SUMIF($AX4:$BH4,SMALL($AX4:$BH4,{1,2,3}),$BV4:$CF4)" and change them so that the large{1,2,3} and small {1,2,3} of $AX4:$BH4 hold for BV4:CF8, i.e. the whole week.
Second: The criteria on the last day of the proceeding loop is the criteria for the first day of the next loop. In other words, thought about in a real life scenario, one can only make a decision for that week based on the criteria of the day before.
Third: If the loop hits a "FALSE" statement (which would happen if the sum of the ranks is less than 45), I need excel to take the next available criteria, and apply that to the next 5 days.
Fourth: This (above statement) means that the loop needs to be linked to a calender. I can't just apply the same criteria to 5 cells, the next criteria to the next 5 cells, ect, because on the FALSE days, there would not be a sum, but instead the sum would pick back up at the next available criteria, and THEN apply that to the next 5 cells.
Even a partial code or input would be helpful. I'm really running on nothing.
