formulasgoogle sheets
When the value in the "Amount Owed" column becomes less than 0, I want the value in the "Year" column to appear under "Year Paid Off."
For example, if the "Amount Owed" dropped below zero at "Year" 1, I would want 1 to appear under "Year Paid Off"
I've tried a few different ways but nothing seems to work. I thought I could just do something like =if(f6:f130<0,c6:c130) but that seems way off.
I think this is what you want. A spreadsheet the generates a column of rand() numbers. Then look up the rand() number and return another value.
In the following instruction be sure to keep the $s for absolute referencing.
=int(RAND()*$E$2)+1 (where cell E2 holds the upper limit of the random numbers).=vlookup(A2,$C$2:$D$8,2) (where cells C2 to D8 hold the substitution values). If the random number generated is 1 then this returns the name Abe.
You identified and solved the issue correctly: =K4 is a relative reference, so when it is applied to the range K7:K26, the following happens:
If this sort of comparison is not what you want, then the absolute reference =K$4 should be used.
Note that the above logic applies equally well to custom formula formatting, and to "greater than", "less than", etc.
Best Answer
if there will be only one possible negative value try this:
E2:
=FILTER(C6:C130;F6:F130 <0)if there will be more the one possible negative values try this:
E2:
=TRANSPOSE(FILTER(C6:C130;F6:F130 <0))