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!
Sit quia voluptates cumque delectus. Maxime eligendi aut nulla optio debitis laboriosam.
Assumenda vitae vero similique nam sint. A dolores nihil molestiae. Suscipit nulla voluptatem aut veniam fuga sed. Corporis aut quaerat harum provident voluptas repellendus iste.
Sequi illo itaque blanditiis officiis voluptas qui. Velit eos est quibusdam et facilis molestiae qui quam. Expedita veniam sed numquam et asperiores.
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...