 • 436K Members
• 10.6K Online
• 521K Conversations

# Complicated formula help

I have a data table that looks something like this, only with significantly more rows. In this data, PR+TC=GL for each set of data in column A. So, for example,

12345 PR + 12345 TC = 12345 GL. In this case \$20 + \$40 = \$75, which is not correct. When it isn't correct, I need the difference to come from the number in the TC row -- so in this example, the TC row would change from \$40 to \$55. Is it possible to create a formula to do this?

 Col A Col B Col C 12345 PR \$20 12345 TC \$40 12345 GL \$75 67890 PR \$30 67890 TC \$50 67890 GL \$80
3 Replies

# Re: Complicated formula help

Please check the formula and result in Column D in the attached file.

Your data has been formatted as an Excel table, this helps to copy down the formula.

The formula also assumes that previous two consequetive rows have PR and TC values for a specific GL item.

# Re: Complicated formula help

Hello @kjpinch,

Assuming "GL" is always directly under "TC" and your table begins in cell A1:

=IF(B3="GL",C3-C2-C1,"") could be a formula for cell D2

Then copy the formula down as necessary.

This would show the value in the TC row that is required to force PR+TC=GL.

# Re: Complicated formula help

@kjpinch wrote:

so in this example, the TC row would change from \$40 to \$55. Is it possible to create a formula to do this?

A cell can either have a value or a formula but not the both.

To change the TC price in the first place, you will need a VBA solution to achieve that.

And if you are open to a VBA solution, please upload a sample file with small subset of your data (after removing any sensitive data) and the layout of your sample file and your original file must be exactly the same.

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies