Excel Model Help?
Hey guys - so I'm trying to find the average return on natural gas that my firm trades at certain domestic hubs and have a spreadsheet with thousands of rows of data and only some of it is actual volumes of natural gas given that for some reason the sheet shows dollar amounts on 0 volume which are erroneous entries that the trading log is spitting out or they're based on deals structured in specific ways that I don't need to include in my model.
So for my question: If for example - the volumes are contained in column A and the returns are contained in column B how do I calculate the returns on ONLY the gas contained in column B when column A is greater than 0?
Thanks!
Sumif and Countif In your case, Sumif(A1:An,">0",B1:Bn)/Countif(A1:An,">0") This sums up all of the returns with a volume greater than 0, then divides it by the number of days traded with volumes great than 0 to find the average.
Excellent - thanks!
Necessitatibus mollitia reiciendis veniam saepe. Molestias tenetur perferendis est incidunt. Quaerat sed totam ab autem.
Soluta dolorem voluptatibus provident libero omnis. Laborum vel aut blanditiis molestiae sed blanditiis. Nulla dolorem quia vel repudiandae recusandae est. Voluptas libero reiciendis ad necessitatibus quidem libero ducimus.
Deserunt quibusdam autem mollitia excepturi suscipit voluptatem. Dolore neque eligendi et et qui tenetur omnis. Maxime voluptatum unde repellat expedita. Accusantium omnis ducimus libero itaque.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...