Create a custom column

This guide explains how to create a custom reusable column, which will make use of Excel or other spreadsheet software formulas. For custom historical data columns, please see

Instructions

On the Data / Columns screen, click Add new Custom Column:

This will launch the Custom Column Builder:

The title is what you will see both in the column chooser as well as in the header cell in Excel.

The description is what will show as the description in the column chooser when you hover over the column. It isn’t used elsewhere and could be a reminder to yourself as to what this column is supposed to do (it might not be obvious from the formula)

Format option

Formatted in Excel as

Format option

Formatted in Excel as

Unformatted

Generic

Date

Date with choice of format: (MM/dd/yy, MM/dd/yyyy, dd/MM/yy, dd/MM/yyyy)

Integer

Number without decimals and with thousands delimiter

Number with Decimals

 

Percentage

Percentage with % symbol

Price

 

Text

Text

True/False

 

Formula:

Arguably the most important part, you can use almost any cell value or formula that is permitted in Excel (or the spreadsheet software of your choice). For example if you are using Google Sheets you can insert an image using “=image(@{132})”.

Formulas that are to be evaluated by Excel need to begin with an equal sign (=). Anything else will just be rendered as typed (but column references will still be resolved).

Column References

You can refer to and use in your calculation any column that exists in PC2, including custom ones. So you can even create building blocks of some formulas and reuse them elsewhere.

To add a column reference simply search for it and click the title.

To add a reference to an input column, use letter references such as @{A}.

Do Columns need to be in the output?

What happens when you remove a column from your output that is used in the formula of another?

If referred columns are missing, Price Checker will transparently embed the formula into any dependent formulas, so the calculations will still work.
If a core column is missing, it’s value will be hardcoded into the formula.

Examples

Let’s assume we’re on row 3:

Custom column formula

References

Rendered in Excel as

Custom column formula

References

Rendered in Excel as

@{14}

14 = AMZ Multi Pack Qty

2

=@{14}

14 = AMZ Multi Pack Qty (e.g. column P)

\=P3

=@{1}+@{85}

1 = Cost (e.g. column C)
85 = Referral Fee (e.g. column AQ)

=C3+AQ3

@{1}+@{85}

as above

2.35+0.38

=max(@{33}, @{42}

33 = BuyBox Price New (e.g. column N)
42 = Lowest MFN New Price (e.g. column Z)

=max(N3,Z3)

Who want’s some @{11}?

11 = Amazon Title

Who want’s some 12Pk Dog treat, beef flavour [long expiry]?

=@{1}+@{85}

1 = Cost (e.g. column C)
85 = Referral Fee (assume not on sheet)

=C3+0.38

Two custom formulas:

  1. =@{1}+@{85}

  2. =@{5006}*10

1 = Cost (e.g. column C)
85 = Referral Fee (assume not on sheet)

5006 = first formula (assume not on sheet)

=(C3+0.38)*10

Allow column to be added multiple times

When this option is selected, it allows you to add this column to the same sheet more than once. Otherwise it will be limited to once per sheet/tab.

Deleting a custom column

You can delete your column just by clicking “delete” and confirming the prompt. A deleted column will also be removed from all sheets. However, PC2 won’t currently check whether you’re using this column in other custom formulas, so be sure to amend those if you need to.

Price Checker does not currently check if your custom formulas contain a column when deleting.