सामान्य सूत्र
=AVERAGE(OFFSET(A1,COUNT(A:A),0,-N))
सारांश
अंतिम 5 डेटा बिंदुओं को औसत करने के लिए, आप COUNT और OFFSET फ़ंक्शन के साथ AVERAGE फ़ंक्शन का उपयोग कर सकते हैं। आप अंतिम N डेटा बिंदुओं को औसत करने के लिए इस दृष्टिकोण का उपयोग कर सकते हैं: अंतिम 3 दिन, अंतिम 6 माप आदि। दिखाए गए उदाहरण में, F6 में सूत्र है:
=AVERAGE(OFFSET(C3,COUNT(C:C),0,-5))
नोट: ऊंचाई का एक नकारात्मक मान Google शीट में काम नहीं करेगा। अधिक जानकारी के लिए नीचे देखें।
स्पष्टीकरण
OFFSET फ़ंक्शन का उपयोग प्रारंभिक संदर्भ के आधार पर गतिशील आयताकार श्रेणियों के निर्माण के लिए किया जा सकता है और दी गई पंक्तियों, स्तंभों, ऊंचाई और चौड़ाई को दिया जा सकता है। शुरुआती संदर्भ से पंक्तियाँ और स्तंभ तर्क "ऑफ़सेट" की तरह कार्य करते हैं। ऊँचाई और चौड़ाई के तर्क (दोनों वैकल्पिक), यह निर्धारित करते हैं कि अंतिम पंक्ति में कितनी पंक्तियाँ और स्तंभ शामिल हैं। इस उदाहरण के लिए, OFFSET इस तरह कॉन्फ़िगर किया गया है:
- संदर्भ = सी ३
- पंक्तियाँ = COUNT (A: A)
- सर्दी = ०
- ऊँचाई = -5
- चौड़ाई = (प्रदान नहीं की गई)
प्रारंभिक संदर्भ वास्तविक डेटा के ऊपर C3 सेल के रूप में प्रदान किया जाता है। चूंकि हम चाहते हैं कि OFFSET स्तंभ C में अंतिम प्रविष्टि से उत्पन्न होने वाली सीमा लौटाए, इसलिए हम आवश्यक पंक्ति ऑफ़सेट प्राप्त करने के लिए स्तंभ C के सभी मानों को गिनने के लिए COUNT फ़ंक्शन का उपयोग करते हैं। COUNT केवल संख्यात्मक मानों की गणना करता है, इसलिए पंक्ति 3 में शीर्षक स्वचालित रूप से अनदेखा कर दिया जाता है।
स्तंभ C में 8 संख्यात्मक मानों के साथ, OFFSET सूत्र निम्नानुसार है:
OFFSET(C3,8,0,-5)
इन मूल्यों के साथ, OFFSET C3 पर शुरू होता है, C11 को 8 पंक्तियों को बंद कर देता है, फिर रेंज C7: C11 बनाने के लिए आयताकार रेंज को "पीछे" 5 पंक्तियों का विस्तार करने के लिए -5 का उपयोग करता है।
अंत में, OFFSET ने रेंज C7: C11 को AVERAGE फ़ंक्शन पर लौटाया, जो उस श्रेणी के मानों की औसत गणना करता है।
एक्सेल बनाम चादरें
इस फॉर्मूले के साथ एक अजीब विडंबना यह है कि यह Google शीट्स के साथ काम नहीं करेगा, क्योंकि शीट्स में OFFSET फ़ंक्शन ऊंचाई या चौड़ाई के तर्क के लिए एक नकारात्मक मूल्य की अनुमति नहीं देगा। एक्सेल डॉक्यूमेंटेशन यह भी कहता है कि ऊंचाई या चौड़ाई नकारात्मक नहीं हो सकती है लेकिन ऐसा लगता है कि 1990 के बाद से एक्सेल में नकारात्मक मूल्यों ने ठीक काम किया है।
नकारात्मक ऊँचाई या चौड़ाई के मान से बचने के लिए, आप इस तरह से एक सूत्र का उपयोग कर सकते हैं:
=OFFSET(C4,COUNT(C:C)-5,0,5)
सूचना C4 इस मामले में शुरुआती संदर्भ है। सामान्य रूप है:
=AVERAGE(OFFSET(A1,COUNT(A:A)-N,0,N))
जहाँ A1 आप की औसत संख्या में पहला सेल है।
