Logical calculation in Excel

0 votes

I need advice/help. I am working on calculation in excel where I have data like mentioned below.

.    A        B        C        D        E        F        G        H         
1|   A275     A277     A273     A777     A777     TOTAL    A222     GRAND TOTAL
2|   5        7        4        3        4        7        7  

Now, I want to count row 2 based on the header.
Here is the condition.
If A1 <> B1 then take A1, if B1 <> C1 then take B1, if C1 <> D1 then C1, so on.

But tricky part is...
If D1<>E1 then D1 else (if E1<>F1 then E1 else (if F1 = "TOTAL" then F1 else(if F1<>G1 then F1)))
In short H2 should have 30 and not 37.

Added comments:------------------------------------
So, Basically if A1<>B1 then take A1 but if A1=B1 then take B1, but then for B1, its a same rule like if B1<>C1 then take B1, but if B1=C1 then take C1 and for C1, same rule. Stopping point will be "TOTAL"Along with these logic I need to check if any cell in row 1 is "TOTAL" then take value for same column. Now this "TOTAL" can be in any cell in row 1.
So from above table my calculation will be 5(A2) + 7(B2) + 4(C2) + 7(F2) + 7(G2) = 30
In this calculation I have not included D2 and E2 as D2=E2 so I took D2, here E2<>F2 so I should have taken E2, but as F2="TOTAL" so I took F2 and not D2 and E2.
I hope this make sense. (Sorry, I know its confusing.)
I have data in more then 100 columns.
Can this be achieved using Macro?
------------------------------------------------------------

Another pain point is data and header are dynamic, so I can't have a fix format. Logic should be in a way that can handle the dynamic data and header.

Any help or suggestion will be greatly appreciated.


 

Nov 7 in Others by gaurav
• 22,040 points
38 views

1 answer to this question.

0 votes

Excel logical functions - overview. Microsoft Excel provides 4 logical functions to work with the logical values. The functions are AND, OR, XOR and NOT. You use these functions when you want to carry out more than one comparison in your formula or test multiple conditions instead of just one.

answered Nov 8 by gaurav
• 22,040 points

Related Questions In Others

0 votes
1 answer

Determine if calculation between 2 date time values is < 72 Hours in excel

Actually, Excel is quite accommodating in this ...READ MORE

answered Nov 12 in Others by narikkadan
• 37,660 points
43 views
0 votes
1 answer

how many logical connectives are there in artificial intelligence?

Negation, conjunction, disjunction, implication, and biconditional are ...READ MORE

answered Jan 7 in Others by Edureka
• 10,880 points
1,402 views
0 votes
1 answer

Spell number in excel 2013 using 2 rows

Hello, there are a few steps to ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
106 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
142 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17 in Others by Edureka
• 13,640 points
109 views
0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3 in Others by narikkadan
• 37,660 points
94 views
0 votes
1 answer

Calculate monthly average from daily data without PivotTable

Assuming you have the months in column D enter ...READ MORE

answered Oct 3 in Others by narikkadan
• 37,660 points
71 views
0 votes
1 answer

Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE

answered Oct 7 in Others by narikkadan
• 37,660 points
92 views
0 votes
1 answer

Change date format of cell in excel from dd.mm.yyyy to yyy/mm/dd ( excel version 2013 )

Hello :)   Excel’s Format Cells function can quickly ...READ MORE

answered Feb 9 in Others by gaurav
• 22,040 points
531 views
0 votes
1 answer

Want to compare two columns in excel

Hello To compare two columns in excel ...READ MORE

answered Feb 9 in Others by gaurav
• 22,040 points
305 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP