The following formula seems working even for negative and decimal values.Hi David,
That formula works for the particular data set and for data containing whole numbers up to about 8 digits long if data can be located anywhere on the sheet. So for things like dates, scores and record numbers it may make sense to use such a formula particularly on large record sets where you would get a significant speed improvement.
However, it would need adapting to allow for negative or decimal values eg currency values or scientific measurements, so it would be good to attach these further details with the challenge description!
A simple function for 3 columns.Hello friends,
This time, three columns.
To find the amount of 53, as it appears in the formula in cell D13, column E is illustrative.
Without the use of volatile functions.
Thanks Lori. Kindly please break this formula for the likes of me who are still new to excel. Thanks in advanceHi David, Can you make additional assumptions on the type of numbers in the range?
For example assuming integers between 0 and 99 and data starting in row 1 you could try with CSE:
The formula quoted by Shrivallabha was allowing for arbitrary numbers like =RAND(). This type of formula would lose precision with those type of values.
Ufoo,Thanks Lori. Kindly please break this formula for the likes of me who are still new to excel. Thanks in advance
This formula here has some parts associated to the previous challenge:
for which Narayan has explained so painstakingly:
If I were you, I'd break head for finding out how it works and if few more brain cells were left at the end of exercise then I'd spend time on how I can use it (formula or concepts) to real life scenario. So if you care and want to gain and apply knowledge you should not ask for tailor made explanation for each posting. Instead, start breaking your head on them
There will be times when you will not understand something but then all you should have is specifics that you don't understand not the whole thing!