CODEWITHSUNDEEP
excelcircular-referenceformulas
Alex Nicol
Alex Nicol

Reputation: 17

fix a cell value in excel after its been calculated by a formula

I have a bit of a circular reference going:-

A1 = Payment amount
B1 = Number of Payments: Created by counting dates payments made
C1 = Commission due in May (A1 x %age depending on B1)
D1 = Commission due in June (A1 x %age depending on B1)

If B1 = 1 % = 100%. If B1 = 2, % = 50%.

This works

However C1 needs to stay at 100% once the formula has generated it. Currently its altering when D1 is then updated (both C1 and D1 read A1 x 50%).

Please Advise.

Upvotes: 1

Views: 1399

Answers (2)

Tarfon
Tarfon

Reputation: 1

I'm in no way an expert in Excel, but I use it to test my knowledge of vocab. I have to generate a random number that triggers the English test word and the I enter the German equivalent. I have to freeze the random number long enough for the entry and the comparison to occur and I use a circular reference formula in CELL D10: =IF(G10="",RANDBETWEEN(1,G12),D10). G10 is the student response cell, and I'm able to hold the question word steady until the English to German comparison is performed and the messages for congratulations/or the correct answer is generated. Is this any use to you? Cheers, George

Upvotes: 0

gssi
gssi

Reputation: 5071

Sorry, you can't have it both ways. A cell either contains a defined value or a calculated value! You have to rethink your layout or redefine your problem statement.

Upvotes: 1

Related Questions