pivot table to calculate EBIT

[ajm]

i have a data set that includes revenue, expenses1 and expenses2 (simplified). if i have a revenue figure of $50, and EXP1 is $20 (displayed as positive) , Exp2 is $10 9also displayed as positive), how do i show the EBIT total ($20)? here is a shot of the raw data:

<!-- Please do not remove this header -->
<!-- Table easily created from Excel with ASAP Utilities (http://www.asap-utilities.com)  -->
<table border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF" cellspacing="0" cellpadding="2">
 <tr>
 <td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Grp</font></td>
 <td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Amt</font></td>
 </tr>
 <tr>
 <td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Revenue</font></td>
 <td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">50</font></td>
 </tr>
 <tr>
 <td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">EXP1</font></td>
 <td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">20</font></td>
 </tr>
 <tr>
 <td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">EXP2</font></td>
 <td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">10</font></td>
 </tr>
</table>

 

[xenou]

Did you get it working? I rather think it depends on the context, amount of data, preferences, etc. More than one way here - some of them being using SumIf or Sumproduct types of formulas, using a query, or coercing the Expenses to negative values (or vice versa with Revenues).

ξ

 

[ajm]

it works when you make Revenue, Expenses, etc into Headings. in my original data set the line items get spat out of another system with a "Group" field identifying them as either Rev or Exp. So was just trying to be a little slicker for the boss. cheers

 

[markmzz]

i have a data set that includes revenue, expenses1 and expenses2 (simplified). if i have a revenue figure of $50, and EXP1 is $20 (displayed as positive) , Exp2 is $10 9also displayed as positive), how do i show the EBIT total ($20)? here is a shot of the raw data:

Ajm,

Post a sample of your data source with the formulas that already exist.

Markmzz