I have a large spreadsheet with a number of forumlas and they all make complete sense apart from one, which is listed below. Does anyone have any idea what this NORMALDIST calculation is trying to acheive or tell me? It has relevants to HE =MAX(1,NORMDIST(3,N18,N18/4,TRUE)-NORMDIST(0,N18,N18/4,TRUE) + 2*(NORMDIST(6,N18,N18/4,TRUE)-NORMDIST(3,N18,N18/4,TRUE)) + 3*(NORMDIST(9,N18,N18/4,TRUE)-NORMDIST(6,N18,N18/4,TRUE)) + 4*(NORMDIST(12,N18,N18/4,TRUE)-NORMDIST(9,N18,N18/4,TRUE)) + 5*(NORMDIST(15,N18,N18/4,TRUE)-NORMDIST(12,N18,N18/4,TRUE)) + 6*(NORMDIST(18,N18,N18/4,TRUE)-NORMDIST(15,N18,N18/4,TRUE)) + 7*(NORMDIST(21,N18,N18/4,TRUE)-NORMDIST(18,N18,N18/4,TRUE)) + 8*(NORMDIST(24,N18,N18/4,TRUE)-NORMDIST(21,N18,N18/4,TRUE)) + 9*(NORMDIST(27,N18,N18/4,TRUE)-NORMDIST(24,N18,N18/4,TRUE)) + 10*(NORMDIST(30,N18,N18/4,TRUE)-NORMDIST(27,N18,N18/4,TRUE)) + 11*(NORMDIST(33,N18,N18/4,TRUE)-NORMDIST(30,N18,N18/4,TRUE)) + 12*(NORMDIST(36,N18,N18/4,TRUE)-NORMDIST(33,N18,N18/4,TRUE)) + 13*(NORMDIST(39,N18,N18/4,TRUE)-NORMDIST(36,N18,N18/4,TRUE)) + 14*(NORMDIST(42,N18,N18/4,TRUE)-NORMDIST(39,N18,N18/4,TRUE)) + 15*(NORMDIST(45,N18,N18/4,TRUE)-NORMDIST(42,N18,N18/4,TRUE)) + 16*(NORMDIST(48,N18,N18/4,TRUE)-NORMDIST(45,N18,N18/4,TRUE)) + 17*(NORMDIST(51,N18,N18/4,TRUE)-NORMDIST(48,N18,N18/4,TRUE)) + 18*(NORMDIST(54,N18,N18/4,TRUE)-NORMDIST(51,N18,N18/4,TRUE)) + 19*(NORMDIST(57,N18,N18/4,TRUE)-NORMDIST(54,N18,N18/4,TRUE)) + 20*(NORMDIST(60,N18,N18/4,TRUE)-NORMDIST(57,N18,N18/4,TRUE)) + 21*(NORMDIST(63,N18,N18/4,TRUE)-NORMDIST(60,N18,N18/4,TRUE)) + 22*(NORMDIST(66,N18,N18/4,TRUE)-NORMDIST(63,N18,N18/4,TRUE)) + 23*(NORMDIST(69,N18,N18/4,TRUE)-NORMDIST(66,N18,N18/4,TRUE)))
Strange question I know, but could not think of where else to ask!!! Cheers
The equation has a series of terms of the form N*[NORMDIST(3N,mu,sigma)-NORMDIST(3N-3,mu,sigma)] where mu is the mean (N18 in the equation), sigma is the standard deviation (N18/4), and with N going from 1 to 23. This appears to be an estimate involving the average of the normal distribution. It would be more rigorous for N to go from minus infinity to plus infinity and it's not clear why this formula truncated the interval to 1..23. Nevertheless, if the person who wrote the equation was calculating the average, then from the properties of the normal distribution you can derive a closed form solution as:
Total of all NORMDIST terms = mu/3 + 1/2
This will be accurate as long as mu (N18) is in the between 0 and 30. If you plug this into the equation you get
=MAX(1,N18/3+0.5)
Hope that helps.