Comparing last two cells entered...

[roxorvoxor]

Hey guys. I have a worksheet given to me by corporate where they expect us to fill numerous rows in a column each day. Once they are entered we are to compare the last two columns and for each row show whether we are trending up or down in a separate column. Let's say I just filled Column AN Rows 5:35 with the percentages for the night shift. I must look at Columns AM and AN Rows 5:35, compare them, and if they are trending up put a ↑ in Column H Rows 5:35 and if it is trending down put a ↓ in Column H Rows 5:35. Is there a formula I could use for this? I'm not looking forward to VBA as I'm a noob with it but I'm sure there is a way to do it there. Below is a screenshot.

 

[etaf]

you could use SLOPE() - that will tell if + or - or flat

SLOPE(AM5:AN5,$AM$1:$AN$1)

or even TREND()

you could hide the value and put a conditional formatting arrow in place

example here
https://www.dropbox.com/s/2l7fqfwmb4qzpvy/trend_etaf.xlsx?dl=0

 

[roxorvoxor]

you could use SLOPE() - that will tell if + or - or flat

SLOPE(AM5:AN5,$AM$1:$AN$1)

or even TREND()

you could hide the value and put a conditional formatting arrow in place

example here
https://www.dropbox.com/s/2l7fqfwmb4qzpvy/trend_etaf.xlsx?dl=0

Thanks. I have to populate this report every week day and there are 83 row that need to be done each time. This may save me a little time since I could copy and paste it however if there is a way to auto-populate it that would be even better. This could be the in-between however.

 

[etaf]

do you replace the data , or add to the bottom ?
if you setup the data as a table - the formula would autopopulate
otherwise you may need a VBA macro

 

[roxorvoxor]

do you replace the data , or add to the bottom ?
if you setup the data as a table - the formula would autopopulate
otherwise you may need a VBA macro

I have to add data into the next column to the right. Unfortunately the format is set by corporate. Otherwise we would continue to use the worksheet we currently do which, in my opinion, is much better. I can make certain changes to the worksheet however I cannot change the basic design. I was thinking a macro was probably what I needed but again I'm just learning to use them and this may take me a bit of time to figure out on my own and is a big time commitment. Looks like I need to request training from the company!

 

[roxorvoxor]

I just found this gem: https://www.youtube.com/watch?v=uL3sxZ8NU1o. Not exactly what I want to do but again it will work.

 

[etaf]

you may want to start a new question specifically for VBA

 

[roxorvoxor]

I think I figured a way to do it quite easily. Using the icon set for each column I will set a formula to subtract the last first column from the last. The icon set will go off that number. Since my rows aren't all adjacent I will change the formula in the first row to the new columns entered. I will then create a macro that will copy that cell, select the cells I need, and paste the formula into the cells. This should take care of the issue with only an added step (changing the cells in the first formula). Easy peasy. Thanks for your help though, I appreciate it!

 

[etaf]

you are welcome