does anyone have the excel formula that would calculate only USD sales for PRO leveling?

Discussion Forums Ask a Question
1

does anyone have the excel formula that would calculate only USD sales for determining PRO levels?

i’m pretty sure it should be an “IF” formula, but it’s too complicated for me to figure out myself. 

for instance: 

“IF [royalty] ends in “USD” COPY [royalty usd] to [new column]”

then that column can just be totaled out..

but i can’t figure out the actual formula… especially because the “TRUE” component is only part of the cell contents. 

does anyone already have this hashed out?

1 Like
2

forgot, it would also have to omit all canceled orders too… but i guess that was obvious. 

3

I gave up on trying to calculate it from the royalty reports - it’s just too complicated what with cancellations and all the extraneous text data mucking up the fields. It’s much easier to just look at the payment history report (the green one) where it breaks down your monthly payment by currency type. You have to make your screen resolution about 200% in order to read the microscopic numbers - at least I do - but the relevant info is there. I just copy/paste the monthly number into a spreadsheet and sum it that way. Much easier than trying to massage the raw data. 

4 Likes
4

@StinkPad  I was just going to suggest an easier way (or the way I did it) was to go into your Payment History and copy/paste all the currencies then just add up the USD’s. Maybe? 

and then I read @Cat  answer. Great minds! (again)

3 Likes
5

Oh,  that’s an interesting idea, using an IF formula. I’m going to play around with that when I get home tonight.

1 Like
6

I don’t know much about Excel, but in case you also use Google Sheets:

  1. Download the Royalty History file “royaltyHistory.csv” and import it into cell A1.
  2. Paste this formula into any empty cell:
    =SUMPRODUCT(IFERROR(REGEXEXTRACT(FILTER($N$2:$N, $P$2:$P=“cleared”, NOT(ISNUMBER(SEARCH(“See Above”, $N$2:$N))), NOT(ISBLANK($N$2:$N))), “$(\d+.\d{2}) USD”), 0))

If you want to know the current status, create another cell, replace “cleared” with “pending” and add up both results.

If you want to see any other currency, replace “USD”

4 Likes
7

The simplest way I found is to use the Edit feature, when pulling the csv reports.  Simply pull cancellations and cleared/pending reports separately.  Then, there’s an easy report to track your cancellations on one report.  And, to answer your question, on the cleared/pending reports, you can either use your Filter function and remove all the “See Above” ones…or use the Find and Replace (to remove them all), and with the Find and Replace, remove all the ** too.  [Of course I use Google Sheets, but they’re pretty similar in excel I think too.].