Hi guys! So I need a little help seeing if there's a way compacting some code down because I don't want to go through every combination of if statements if they are possible to be avoided, but my vba knowledge (and formula knowledge) is limited. So basically, I have this table (here's an example of how it could look):
I just filled out one sample set and I'll explain how it works. So this table is a specific data points pulled from another sheet (not important to this), but each year gets its own "rating" of sorts, rated from 1-4 (these could be decimal number, doesn't have to be a whole number) The maximum number of years of data is 5 and the minimum number of years is 1. The total column is a weighted formula that we used to give us a number we use later on, but recently we've been having some issues getting it. So for five years, our formula looks like this (below, note that Rate1, 2, 3... is the Rating in Year 1, 2, 3... (rated 1-4) and the Year variable is just the year integer).
Formula (note that right now it's in VBA in my workbook, for a 5 year rating):
Formula (note that right now it's in VBA in my workbook, for a 4 year rating):
Hopefully you see the pattern we use for the formula now.
This works fine so long as all the ratings are 1-4 as they should be. But now, for a few items we don't have the data for a few years, so they are currently inputted as a 0. What we are trying to do, is get it so that if a rating is a 0, to replace it with the previous year's rating. So in the table above for example, if the "Year 4" rating was a 0, it would be replaced by Year 5's rating instead. (Note that if year 5's rating is zero, since we don't have previous data, we just ignore it and use a 4 year formula instead.) We also want this to work if there are multiple 0's (2 to 3) so that it pulls from the previous years rating. If there is only 1 rating or no ratings that are 1-4 then the formula returns 0. Hopefully I've described this clearly, but if there are any questions or clarifications please let me know! And thanks in advance for any help!
| Item # | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | Total (Formula Driven) |
| 101 | 4 | 3.5 | 3 | 2 | 1 | See formula below |
| 102 | ||||||
| 103 | ||||||
| 104 | ||||||
| 105 | ||||||
| 106 | ||||||
| 107 | ||||||
| 108 |
I just filled out one sample set and I'll explain how it works. So this table is a specific data points pulled from another sheet (not important to this), but each year gets its own "rating" of sorts, rated from 1-4 (these could be decimal number, doesn't have to be a whole number) The maximum number of years of data is 5 and the minimum number of years is 1. The total column is a weighted formula that we used to give us a number we use later on, but recently we've been having some issues getting it. So for five years, our formula looks like this (below, note that Rate1, 2, 3... is the Rating in Year 1, 2, 3... (rated 1-4) and the Year variable is just the year integer).
Formula (note that right now it's in VBA in my workbook, for a 5 year rating):
VBA Code:
= (((Rate1 - Rate2) * 4) + ((Rate2 - Rate3) * 3) + ((Rate3 - Rate4) * 2) + (Rate4 - Rate5)) / (Year1 - Year5)
VBA Code:
= (((Rate1 - Rate2) * 3) + ((Rate2 - Rate3) * 2) + (Rate3 - Rate4)) / (Year1 - Year4)This works fine so long as all the ratings are 1-4 as they should be. But now, for a few items we don't have the data for a few years, so they are currently inputted as a 0. What we are trying to do, is get it so that if a rating is a 0, to replace it with the previous year's rating. So in the table above for example, if the "Year 4" rating was a 0, it would be replaced by Year 5's rating instead. (Note that if year 5's rating is zero, since we don't have previous data, we just ignore it and use a 4 year formula instead.) We also want this to work if there are multiple 0's (2 to 3) so that it pulls from the previous years rating. If there is only 1 rating or no ratings that are 1-4 then the formula returns 0. Hopefully I've described this clearly, but if there are any questions or clarifications please let me know! And thanks in advance for any help!
