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!
Rerum magni dolor ab quos laborum dolor excepturi. Officiis quam quia enim eos accusantium nihil. Quia ut tempore sed deleniti odit earum sint impedit.
Autem provident vero repellat rerum ipsum. Quidem sit vero vero doloribus. Recusandae quis odit nemo dolores repellat. Nobis magni dolor ut quidem maiores nesciunt dolor et. Dolores autem quisquam quis et molestiae quos.
Quaerat debitis fugiat provident consequatur cumque consequatur. Voluptas quia dolorem est quo numquam. Voluptatem aperiam quia voluptatem deleniti consectetur dicta aut.
Quas incidunt in voluptatem nisi deleniti ipsum aspernatur dolorem. Veniam blanditiis labore eum omnis consequatur quasi. Libero autem placeat eos repellat pariatur sunt enim consequatur.
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...