8 Calculated Field & Items

Posted by

8. CALCULATED FIELD & ITEMS 🧠

👇 Watch & share if you love this content!

🖥️ Check this out: Pivot Table - Calculated Filed & Items

Excel-ல Pivot Table தான் நல்ல summary தரும். ஆனா அதுல Calculated Field & Calculated Item use பண்ணினா, அதுவே Super Smart Report ஆயிடும்! 🤖

🔷 8.1 – Creating a Calculated Field

🧠 Goal: நம்மக்கு Sales & Cost இருக்கு. Profit = Sales - Cost கண்டுபிடிக்கணும்.

🧾 Example:

Field     Values
Sales     5000
Cost     3000

📍 Steps:

  • Pivot Table select பண்ணுங்க

  • PivotTable Analyze ➡️ Fields, Items, Sets ➡️ Calculated Field

  • Name = "Profit"

  • Formula = = Sales - Cost

  • ✅ OK

🎯 Result: Profit column புது column-ஆ Pivot Table-ல add ஆகும்! 💰

🔷 8.2 – Use Existing Calculated Field in Another Formula

🧠 Goal: Calculate 10% Commission based on existing Profit
🧾 Already created Profit field இருக்கு. இப்போ 10% Commission figure வேண்டும்.

📍 Formula: = Profit * 0.10
📌 Use that in another new Calculated Field – name it "Commission"
🔽 Result: Commission = 200

✨ இது போல existing field-ஐ re-use பண்ணலாம் future calculations-க்கு. 👌

🔷 8.3 – Editing a Calculated Field

🧠 Goal: Update Profit formula to subtract Discount too
🤔 Suppose earlier formula = Sales - Cost,
அதில் Discount உண்டு என்று புதுசா update பண்ணணும்.

📍 New Formula: = Sales - Cost - Discount
🔽 Result: Profit = 5000 - 3000 - 200 = 1800

➡️ Calculated Field → Select "Profit" → Update formula → ✅ OK
🎉 Updated result instantly show ஆகும் in Pivot Table! ⚡

🔷 8.4 – Excel Formulas Allowed in Calculated Fields

🧠 Goal: Use IF function to check up values
✅ Allowed:

  • Simple math operators: +, -, *, /

  • Functions like: IF(), ROUND(), ABS()

🧾 Example:
= IF(Sales > 5000, "High", "Low")
➡️ High or Low value appear based on condition

🚫 Not Allowed:

  • VLOOKUP(), INDEX(), MATCH(), SUMIF() 😕
    📌 Use basic logic only!

🔷 8.5 – Creating a Calculated Item

🧠 Goal: Combine multiple regions into one group
🤓 Calculated Field ஒரு whole column-க்கு
📌 Calculated Item ஒரு specific item-க்கு logic apply பண்ணும்

🧾 Example:
Region Field = North = 3000, South = 2000
We want to combine → NorthSouth = = North + South

📍 Select a cell under Region field →
Fields, Items, Sets ➡️ Calculated Item → Name it = "NorthSouth" → Formula = = North + South

🎯 Now Pivot-ல் ஒரு extra Region வரும்: NorthSouth = 5000 ✅

🔷 8.6 – Use Existing Calculated Item in New Formula

🧠 Goal: Add another region to NorthSouth
Already created NorthSouth? Now want to add East:

🧮 New Item = = NorthSouth + East
➡️ Name = "All Regions"

📌 Step by step logic build பண்ணலாம் like Lego! 🧱

🔷 8.7 – Editing a Calculated Item

🧠 Goal: Fix a mistake or update region grouping
🤓 Created wrong formula?

🧾 You wrote = North + West, but should be = North + South
➡️ Go to Calculated Item → Select item → Update formula → Save ✅
🎉 Pivot updates immediately!

💡 Tip: Always name items properly like Region_Total, HighValue_Item etc.

🔷 8.8 – Excel Formulas in Calculated Items

🧠 Goal: Subtract one category from another
✅ Allowed:

  • Math: +, -, *, /

  • Combine items directly

🧾 Example: = East - West
➡️ Difference between sales in East & West regions

🚫 Not allowed:

  • No cell references (like A1, B2) ❌

  • No advanced Excel formulas ⚠️

🔷 8.9 – Calculated Item in Column Labels

🧠 Goal: Subtract one category from another
💡 Calculated Items mostly rows-க்கு தான்.
But, column-ல (like Month: Jan, Feb) கூட use பண்ணலாம்.

🧾 Example:
Jan = 4000, Feb = 5000
📍 Create Item = = Jan + Feb ➡️ Name = "Q1 Total"

✅ Result: New column label = Q1 Total = 9000 🎉

⚠️ Note: More calculated items = More data = Slow sheets!

🔷 8.10 – Solve Order for Calculated Items

🧠 Goal: Set which formula runs first
🎯 Suppose you have:

  • A = North + South

  • B = A * 10%

Excel needs to apply A first, then B.

📍 Go to:
Analyze tab → Fields, Items, Sets → Solve Order
➡️ Move A above B ✅

📌 Proper Solve Order = No calculation errors 🧠

🔷 8.11 – List All Calculated Fields & Items Formulas

🧠 Goal: View all formulas in one sheet for clarity
📋 Want to see all formulas in one go?

📍 Go to:
PivotTable Analyze → Fields, Items, Sets → List Formulas

🎉 Excel will create a new sheet with:

Type Name Formula
Field Profit = Sales - Cost
Field Commission = Profit * 0.10
Item NorthSouth = North + South

✅ Use this for documentation, review, or sharing with others 🗂️

🔚 Final Recap – Summary Table 📌

🔢 Topic 📌 Action 🧮 Example
8.1 Create Field          = Sales - Cost
8.2 Use Field Again          = Profit * 0.1
8.3 Edit Field          = Sales - Cost - Discount
8.4 Use Formula          = IF(Sales>5000,"High","Low")
8.5 Create Item          = North + South
8.6 Extend Item          = NorthSouth + East
8.7 Edit Item          = North + East
8.8 Math in Item          = East - West
8.9 Use in Columns          = Jan + Feb
8.10 Solve Order                 A before B
8.11 List Formulas                 Auto-list in new sheet

🌟 Quick Recap – Cheat Sheet 📋

💡 Feature 📝 Summary
🔢 Calculated Field           Column-level formula inside Pivot Table
🧮 Calculated Item           Specific value-level formula inside a field
✏️ Edit           Update via dialog box easily
🔄 Reuse           Use old logic in new formulas
📜 List Formulas           One-click list of all formulas
⚠️ Solve Order           Control calculation priority

🔚 Final Touch 🎨

✅ Calculated Fields & Items = No external formulas needed!
✅ Clean, dynamic, embedded logic!
✅ Perfect for automated reports, dashboards & real-time analysis! 🚀


Comments

Post a Comment