jondavis1987
Active Member
- Joined
- Dec 31, 2015
- Messages
- 443
- Office Version
- 2019
- Platform
- Windows
So I have a chart with a line plotted from range N9:N19. N9 has the following formula.
I have dragged the formula down through N19 so that C6:I6 is always the same in the sumproduct but C9:I9 will always move down becoming C10:I10 and so on with the copying. The if formula is because sometimes there won't be anything in Column I or G or so on.
C6:I6 are percentages of different types of material and needs to always total 100 when added together. C9:C19 are specific properties of the material in C6 and so on for C9:I19. C9:I19 can't be changed. So N9:N19 is essentially how all of those things blend together and is what's charted.
So here's where I've seen a software (not excel) do what i'm trying to do. I'd like to drag points on the line so to certain places and it will adjust the numbers in C6:I6 to create the desired effect of the point while making all of C6:I6 equaling 100.
Excel Formula:
=IF($I$6>0%,SUMPRODUCT(C9:I9,$C$6:$I$6),IF($H$6>0%,SUMPRODUCT(C9:H9,$C$6:$H$6),IF($G$6>0%,SUMPRODUCT(C9:G9,$C$6:$G$6),IF($F$6>0%,SUMPRODUCT(C9:F9,$C$6:$F$6),SUMPRODUCT(C9:E9,$C$6:$E$6)))))C6:I6 are percentages of different types of material and needs to always total 100 when added together. C9:C19 are specific properties of the material in C6 and so on for C9:I19. C9:I19 can't be changed. So N9:N19 is essentially how all of those things blend together and is what's charted.
So here's where I've seen a software (not excel) do what i'm trying to do. I'd like to drag points on the line so to certain places and it will adjust the numbers in C6:I6 to create the desired effect of the point while making all of C6:I6 equaling 100.
