Flood Frequency Analysis Tutorial in Excel using Gumbel's Method

in this video we're discussing about the flood frequency analysis using gum Bells method so let's started first of all we have the discharge data from the 1980 to 2018 for a station okay uh you can find the data in the authority of the governments related to the hydrology and Metrology okay here and we have the discharge the maximum discharge particular here make sure that this discharge is not your mean discharge but the maximum instantaneous discharge okay there's quite difference and I want to request you to please go through the theory at least once to understand the basics okay the next column we have is the Q that is a discharge okay so we have particular year and the maximum discharge for that year okay what we do is we need to rank the discharge in the descending or means the maximum value at the top and so on so let me first copy all these values and paste it here is I want to sort it in the descending order okay so I go to data and go to short I'll say continue with the current selection and short here I want to select order that is largest to smallest and I click okay so you see that now it is been sorted in the descending order okay the next column we have the rank uh the variable spirit is M so the first rank would be rank one and so on so let me create this + one and let me expand the formula okay so these are the corresponding rank for his discharge okay what we do next is we want to calculate the probability so for the formula probability is M that is divided by n + 1 so m is our rank n is the total number of data in our case total number of data is 39 okay so let's me let me apply the formula is equal to m m is this uh d8 value okay M that is divided by n + 1 n is our uh total value that is this + one now it would work for this set of data but I I want to expand it so if I expand it this b47 might change to another uh cell right so I want to keep it constant for that let me apply the dollar and dollar so even if we change drag the formula this value would be constant so I press enter and now let me expand the formula so this is our probability right now we want to calculate the return period written period is nothing but the reciprocal of probability so I'll go for equal to 1/ by and probability so let me expand it okay the next thing we need is YT okay so how do we calculate the YT and what is this YT YT is actually the reduced variate okay and to calculate the YT we have the formula minus Ln whole bracket Ln T upon T minus one so basically YT is the function of uh return period so what we do is we need to apply the Ln function so is equal to Ln sorry negative Ln minus Ln bracket again Ln here you go with t this is T that is divided by I'd go for again T minus 1 bracket close and bracket close I press enter okay now we have our right let me expand it the next thing we need is KT okay KT is the frequency Factor now to calculate the KT you have to estimate YT YN and SN okay so what are YN and SN so YN and SN are the reduced standard and reduced mean and they are the function of the uh number of sample okay so before that let me uh calculate all those mean median things okay let me remove this I don't need this for example okay I need to retain the N let me calculate the maximum Within These values okay from our uh uh data of discharge let me calculate the maximum value for that we apply Max and let me select the range that is from here to the last one okay so this is the maximum value for mean simply go to mean and select the same range for median sorry equal to and median again the same range that is this okay for mean we need to have average average and same range standard deviation that is equal to STD ev. s and the range the next one is is Q it uh it informs that what is the orientation of the distribution right it's left skewed right skewed okay for that we just go to equal to and SK and select the range okay so you have YN and SN so YN and SN as I said earlier they are functions of uh the number of data okay our number of 39 now to calculate the number of data you can use the count function okay apply is equal to count and select the range it's better to apply formula because for small number of data it might be easy for the larger number of data it's not uh you know convenient to count each and every so you just apply the formula it will count okay so for w na our n number of data is 39 so here is our uh table for YN and SN so for each sample size there particular value of YN and SN okay in our case we have uh and 39 so for 39 let me go to 39 and say here is these two values are the values for uh YN and SN okay for 39 you can manually copy and paste them there but I suggest you to apply the formula the V looka formula because again the same thing here the data is limited so you could easily manually you know copy and paste but what if the data we have thousands of data right so it's always better to apply the V up so let's learn how to lose V lookup I is equal to vook up we look up now which value we want to look we want to look this n value this one and what's the table area table area is out this let me select the table area column index is which column for YN we want uh one two gell two and I wanted exact match what happened here sorry I need to have false I want the exact match false still not working what happened here we look up uh b47 at gbell two oh no there's an error it's not gbell right let me rewrite the formula uh let's it's V look up the value we want to look up is this and the table we want is from here so let me select the area and we want to go to this second column okay for YN we to select two and we want exact exact math so I'll type false and press enter so you get 04 543 for 39 yeah for 39 the corresponding value of YN is 0.453 you could directly copy and paste it as I said earlier but it's better to use formula I strongly recommend to use the formula okay similarly for SN it's almost same formula except for the uh column index okay so I can copy the formula simply copy it and uh paste formula what I want to do is I want to edit it our n is b47 so I'll be searching for b47 and the index number would be three in now case okay and let me recheck it for 39 1. 138 exactly 1138 so this is how you estimate the YN and SN okay using p look up okay now our task is to calculate the KT so for KT we need YT YN and SN uh YT we've already estimated YN and SN we have estimated so let us calculate KT that is equal to YT minus YN YT is uh G8 value AB Sate minus YN is this value and it is to be divided by SN SN is this value so again uh in our case Y and S they are constant so let me put the dollar sign over here and here okay and press enter so it is calculated I want to drag it up to here okay now I want to calculate the uh QT that is Q predicted uh for that we have the formula XM + KT SN minus one KT Sigma n minus one uh that is the XM aage XM average discharge value and KT is corresponding KT and sigma n minus one is our standard deviation okay so let me apply the formula is equal to XM means the average value that we have calculated uh here the average Value Plus KT that is this is the KT into Sigma n minus one the standard deviation uh that is we have here standard deviation and in this formula KT would be changed so let me put the dollar sign here in XM I want to keep it constant and sigma n minus one also constant so I'll put it this way okay and let me expand it now since I am uh calculating the flood frequency analyis performing the FL analysis I want uh these values for different years maybe 2 year 5 year 10 50 100 500 so on right up to, 10,000 years now this small set of data it won't be reliable okay the more data you have the better result would be uh so let me expand this value for different years this for this and and like this okay now we have calculated the predicted discharge uh we have the written period and we have the observed discharge okay so what I'm going to do is I want to plot the data for that uh let me calculate the let me enter here written period so here you have written period let me copy it and paste here sorry I have to paste as value okay the next thing is Q observed Q observed would be our corresponding ranked Q okay so let me copy it and paste here next is Q predicted Q predicted is our QT so I'll be copying QT values and I'll paste here no no I have to past as values right okay now let me plot the data okay so for that I want to select all the three columns and go to insert and go to scatter plot insert scatter plot okay I'm doing this now we have this graph but uh it doesn't look so meaningful here so look at the uh difference in the AIS values okay so what I want to do is for the xaxis for the period I want to make it logarithmic so I to select the axis right click on it and go to forat axis and I want it to be logarithmic scale okay and what I want to do with the y axis is I want it to start from uh say 3,000 so I will apply here 3,000 okay the next thing I want to do is I want to draw a best fit line so I want to select the uh this values predicted values and right click add trend line and select the logarithmic okay so that's how it looks the uh blue one they are the Q observed the blue dots the orange dots they are the uh predicted discharge okay so this is how we have performed uh the flood frequency analysis using the gums method

Channel: Solution Seeker

Views: 1,193

Keywords:

Id: YvKe9ZeE25A

Length: 19min 18sec (1158 seconds)

Published: Tue May 07 2024