Pivot tbl. calculated field - sum of two "regular" fields but only if Field1 matches certain value?

astrbac

Board Regular
Joined
Jan 22, 2015
Messages
55
Hi all,

I have a pretty simple problem - I wish to create a calculated field in my pivot table. This calculated field needs to SUM two fields, that already exist in the data source, but with a little twist. Here is an example which will make this much more clear.

Source data:

MarketPlatformWeb sales $Mobile app sales $
FRios13238709
ITios124347657
FRandroid2342352355
ITandroid1232323434

<tbody>
</tbody>



The result pivot table that I need to create:

MarketTotal sales $
FR(Web sales + Mobile sales)
IT(Web sales + Mobile sales)

<tbody>
</tbody>



The problem:

In the source data, field:Mobile app sales $ needs to be divided by 2 IF field:Platform equals "iOS". (We have a data collection problem that cannot be corrected for now so I have to make do). Yes, I know that one option is to simply intervene in the source data sheet but I wish to avoid that as much as possible. Pseudo-formula would look like this:



This is what I tried but it's not working:


Code:
='Website conversion value' +IF(Platform=OR("iPhone"; "iPod"; "iPad");'Mobile app purchases conversion value' /2;'Mobile app purchases conversion value' )



If I solve this on the pivot table side, then I can simply refresh my source data with new .csv imports which I append to the end of existing data.

Many thanks!
Alex
 
Last edited:

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,264
Office Version
  1. 365
Platform
  1. Windows
Alex

Unfortunately you can't use an IF statement in a calculated field for text values.
 

astrbac

Board Regular
Joined
Jan 22, 2015
Messages
55
Dang :( ... so I WILL have to create a helper column in source data, have it "listen" for incoming dataset and when it finds iPhone, iPad, iPod write 1, for all else it should write 0. Then use that helper column in calculated field formula, right?

Thanks for your time Norie!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,270
Messages
5,623,737
Members
415,986
Latest member
C_Braga

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top