Forum Discussion
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 difference between my bid and every other bid. I plan to filter this data by the project number so the filtered data will only show one row that says CCI as the bidder. The unfiltered version shows multiple rows that have CCI because it shows every project. How can I show the difference between the bids for each project?
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)
6 Replies
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)
