Securing the value in a cell
Hi!
Say I have a small shop at a sports stadium, and want to calculate profits on each product I sell for the season.
The way I’ve done it is having three sheets; ‘sales’, ‘cost’ and ‘result’.
In the ‘cost’ sheet, I have
A1 product name
B1 amount I sell it for
C1 amount I bought it for
In the ‘sales’ sheet, I have
Column A: Product names
Column B-> G: Opponent for the team
Row 2: Number of sales
Example:
A2: Popcorn B2: 52 (sales vs Miami), C2: 15 (sales vs Tampa) etc
In the ‘result’ sheet, I have:
A2 Popcorn
B2 The amount of popcorn sold in total (B2:G2 from ‘sales’))
C2: B2*’cost’!b1 (amount of popcorn x sale price)
D2: B2*’cost’!c1 (amount of popcorn x amount I purchased the popcorn for)
E2: C2-D2 (turnover minus cost)
All this is fine, and this is obviously a simplified version of it.
BUT! Halfway through the season, my provider increased their prices so my total result will be lower. If I change the prize on ‘cost’ C1, it will change all of the sales I’ve had, including the time before the price went up.
Is there a way that I can freeze the values in the cells from the beginning of the season so it’s not affected by the changing of the purchasing price moving forward?
Basically, if I buy the popcorn for $5 and sell it for $10 in May, but buy it for $6 and sell it for $10 in June that will have an effect on my total revenue.
I hope this made sense and that anyone can help me!
Cheers,
Frank
Hi! Say I have a small shop at a sports stadium, and want to calculate profits on each product I sell for the season. The way I’ve done it is having three sheets; ‘sales’, ‘cost’ and ‘result’. In the ‘cost’ sheet, I have A1 product nameB1 amount I sell it forC1 amount I bought it for In the ‘sales’ sheet, I haveColumn A: Product namesColumn B-> G: Opponent for the teamRow 2: Number of sales Example:A2: Popcorn B2: 52 (sales vs Miami), C2: 15 (sales vs Tampa) etc In the ‘result’ sheet, I have:A2 PopcornB2 The amount of popcorn sold in total (B2:G2 from ‘sales’)) C2: B2*’cost’!b1 (amount of popcorn x sale price) D2: B2*’cost’!c1 (amount of popcorn x amount I purchased the popcorn for) E2: C2-D2 (turnover minus cost) All this is fine, and this is obviously a simplified version of it. BUT! Halfway through the season, my provider increased their prices so my total result will be lower. If I change the prize on ‘cost’ C1, it will change all of the sales I’ve had, including the time before the price went up. Is there a way that I can freeze the values in the cells from the beginning of the season so it’s not affected by the changing of the purchasing price moving forward? Basically, if I buy the popcorn for $5 and sell it for $10 in May, but buy it for $6 and sell it for $10 in June that will have an effect on my total revenue. I hope this made sense and that anyone can help me! Cheers, Frank Read More