Forum Discussion
Solved
Calculate between rows based on another column
I have a bid sheet with raw bid data as shown in the table below. Every project I enter my bid and am always the top bidder in the row (CCI). I am trying to create another column that shows the diffe...
summit20 , try like
Difference = var _1 = Minx(filter(Sheet1,[Project #] =EARLIER([Project #]) && Sheet1[Bidder]="CCI"),[Bid Amount]) return if(ISBLANK(_1),BLANK(),[Bid Amount]-_1)
summit20 , Can you share sample data and sample output in table format?
amitchandak Here is what the raw data would look like. Notice the difference column would be some sort of calculated column.
| Project # | Bidder | Bid Amount |
| 1 | CCI | 10 |
| 1 | ABC | 11 |
| 1 | DEF | 12 |
| 2 | CCI | 50 |
| 2 | ABC | 55 |
| 2 | DEF | 60 |
| 2 | GHI | 65 |
Here is what I would hope the output would look like.
| Project # | Bidder | Bid Amount | Difference |
| 1 | CCI | 10 | - |
| 1 | ABC | 11 | 1 |
| 1 | DEF | 12 | 2 |
| 2 | CCI | 50 | - |
| 2 | ABC | 55 | 5 |
| 2 | DEF | 60 | 10 |
| 2 | GHI | 65 | 15 |
summit20 ,
Difference = var _1 = Minx(filter(Sheet1,[Project #] =EARLIER([Project #]) && [Bid Amount] <EARLIER([Bid Amount])),[Bid Amount]) return if(ISBLANK(_1),BLANK(),[Bid Amount]-_1)amitchandak . We are so close! That works as long as CCI is the lowest bidder. I changed the Bid in Project 2 to 105 and it based the calculation off the lowest bidder. Is there a way to make all of the formulas based off of CCI's bid instead of the lowest bidder?
Project # Bidder Bid Difference 1
CCI 10 1 ABC 11 1
1 DEF 12 2 2 CCI 105 50 2 ABC 55 2 DEF 60 5 2 GHI 65 10 summit20 , try like
Difference = var _1 = Minx(filter(Sheet1,[Project #] =EARLIER([Project #]) && Sheet1[Bidder]="CCI"),[Bid Amount]) return if(ISBLANK(_1),BLANK(),[Bid Amount]-_1)