[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 490

# Referencing the cell above the current cell in MS Excel

Hello,

I'm doing some calculations in MS Excel, and I want to be able to include a cell reference to the cell above in an IF statement.

For example, let's say I only want to perform an operation IF a desired value exists in the cell directly above my if statement.

Say my if statement is in cell C4, =sumif([c3.value equals 'Car'])

Thanks
0
--TripWire--
• 12
• 10
1 Solution

EngineerCommented:
=if(c3="Car",a1+b1,"")

This formula will show the value of A1+B1 if cell c3 contains Car otherwise it will show nothing
0

Author Commented:
I'm trying to avoid using constants....because my "c3" could be a range of values.
Sorry, I should have specified that before.

Let me clarify:
I want to sum the values in D7:U7 IF and only IF the cells in the range D6:U6 = 'Car'
In other words, not all the values in D7:U7 will be added together, but only the ones that correspond to the above value of 'Car'.
0

EngineerCommented:
=sumif(d6:u6,c3,d7:u7)
0

Author Commented:
Instead of simplifying it, maybe I should show you exactly which formula I'm using.

=(((SUMIF(\$D\$6:\$W\$6,\$AJ\$4,\$D7:\$W7))/COUNTIF(D6:W6,"HW")-1)*AJ5)+
(((SUMIF(D6:W6,AK4,D7:W7))/COUNTIF(D6:W6, "CW")-1)*AK5)+(AE7*0.4)

You will see that there are two parts to this formula.  Where it says "(SUMIF(\$D\$6:\$W\$6,\$AJ\$4,\$D7:\$W7)" and...
"(SUMIF(D6:W6,AK4,D7:W7)"  ....I want to subtract the lowest value in the range, BUT ONLY for ONE of the two parts of the formula....whichever is lower.

For example

boat     car     car     boat     boat     car
10         5         7         3          1           8

when I calculate the average, I want to drop the lowest value, but only from the part of the formula that applies to that particular vessel.
So if "((SUMIF(D6:W6,AK4,D7:W7)" is the part that searches for boat, I want it to subtract that minimum value once.

I hope that makes sense.
0

EngineerCommented:
Can you tell me what is the result you are expecting for the boat-car example and how?

Better if you can upload a sample file.
0

Author Commented:
In the example above, I want Excel to add all the values for boat together and all the numbers for car together (separately).  From these two numbers, I want to drop the lowest value from ONE of the numbers.  So in the example given above it would be subtract 1 from the total of boat.   In the file attached, it would be subtract 23 from the total of car.

After that, I want to find the average for boat...and the average for car to be calculated (separately).

Furthermore, there are two percentage values in AJ4 and AK4.  From the above averages, I want to calculate the AJ4 percentage of boat and the AK4 percentage of car.

=(((SUMIF(\$D\$6:\$W\$6,\$AJ\$4,\$D7:\$W7))/COUNTIF(D6:W6,"boat")-1)*AJ5)+
(((SUMIF(D6:W6,AK4,D7:W7))/COUNTIF(D6:W6, "car")-1)*AK5)+(AE7*0.4)

This is the formula I'm working with currently.  You will notice that I have an error in that I'm subtracting 1 from the count of boat and car, when I really just want the entity with the lowest value to have 1 subtracted from its count.
0

Author Commented:
boat-car-example.xlsx
0

EngineerCommented:
If I understood you correctly this formula will give you what you are looking for

=(SUMIF(\$D\$6:\$W\$6,AJ4,\$D\$7:\$W\$7)-IF(AJ4=INDEX(\$D\$6:\$W\$6,MATCH(MIN(\$D\$7:\$W\$7),\$D\$7:\$W\$7,0)),MIN(\$D\$7:\$W\$7)))/COUNTIF(\$D\$6:\$W\$6,AJ4)+(SUMIF(\$D\$6:\$W\$6,AK4,\$D\$7:\$W\$7)-IF(AK4=INDEX(\$D\$6:\$W\$6,MATCH(MIN(\$D\$7:\$W\$7),\$D\$7:\$W\$7,0)),MIN(\$D\$7:\$W\$7)))/COUNTIF(\$D\$6:\$W\$6,AK4)
0

EngineerCommented:
Maybe not.....still working
0

EngineerCommented:
In the formula posted above why do you subtract 1 from both car and boat whereas your description says subtract 1 from car or 23 from boat?
0

Author Commented:
I noted that in my description above....

This is the formula I'm working with currently.  You will notice that I have an error in that I'm subtracting 1 from the count of boat and car, when I really just want the entity with the lowest value to have 1 subtracted from its count.
0

EngineerCommented:
Try this

=(SUMIF(\$D\$6:\$W\$6,AJ4,\$D\$7:\$W\$7)-IF(AJ4=INDEX(\$D\$6:\$W\$6,MATCH(MIN(\$D\$7:\$W\$7),\$D\$7:\$W\$7,0)),MIN(\$D\$7:\$W\$7)))/COUNTIF(\$D\$6:\$W\$6,AJ4)*AJ5+(SUMIF(\$D\$6:\$W\$6,AK4,\$D\$7:\$W\$7)-IF(AK4=INDEX(\$D\$6:\$W\$6,MATCH(MIN(\$D\$7:\$W\$7),\$D\$7:\$W\$7,0)),MIN(\$D\$7:\$W\$7)))/COUNTIF(\$D\$6:\$W\$6,AK4)*AK5+(AE7*0.4)
0

Author Commented:
Very close.

Using your formula, the result of the figures in my sample file is: 43.75543
When I calculate manually, my total is: 48.15149
0

EngineerCommented:
Does AE7 contain any value?

Can you give me values separately for

Sumif for car
Sumif for boat
countif for car
countif for boat
average for car
average for boat
AE7*0.4
0

Author Commented:
boat               car

sum   787               659
countif 11                 9
avg     71.55785      82.42614

There is nothing in AE7
0

EngineerCommented:
Here you go

=(SUMIF(\$D\$6:\$W\$6,AJ4,\$D\$7:\$W\$7)-IF(AJ4=INDEX(\$D\$6:\$W\$6,MATCH(MIN(\$D\$7:\$W\$7),\$D\$7:\$W\$7,0)),MIN(\$D\$7:\$W\$7)))/(COUNTIF(\$D\$6:\$W\$6,AJ4)-(AJ4=INDEX(\$D\$6:\$W\$6,MATCH(MIN(\$D\$7:\$W\$7),\$D\$7:\$W\$7,0))))*AJ5+(SUMIF(\$D\$6:\$W\$6,AK4,\$D\$7:\$W\$7)-IF(AK4=INDEX(\$D\$6:\$W\$6,MATCH(MIN(\$D\$7:\$W\$7),\$D\$7:\$W\$7,0)),MIN(\$D\$7:\$W\$7)))/(COUNTIF(\$D\$6:\$W\$6,AK4)-(AK4=INDEX(\$D\$6:\$W\$6,MATCH(MIN(\$D\$7:\$W\$7),\$D\$7:\$W\$7,0))))*AK5+(AE7*0.4)
0

Author Commented:
I want to accept this as the solution, but I'm finding when I plug in different sets of numbers, and calculate it formulaic vs. manually, the numbers are off by a few digits.  Typically 5 or 6.
0

Author Commented:
In some cases, I'm getting a value that's much larger than what's expected.
I put in a series of low values (with one or two high values) and I got an average in the 50's.

I feel like this is very close, but there might be a need for a small tweak somewhere.
I really appreciate your help so far.
0

EngineerCommented:
Can you throw in a few examples with values?
0

Author Commented:
[Sorry for the delay, Internet was down]

If you look at the attached file, I'm only having a problem with row 9 for some reason.
Maybe it has a problem with too many zeros?
boat-car-example.xlsx
0

Author Commented:
I've found a couple of mathematical errors in some of my work, which cleared up some of the errors, so the I'm thinking that the formula you gave me is working, however, when I move the formula out of the test spreadsheet and into the real version, the numbers get skewed again.

I tried clearing all contents/formats on the cell, but it's still off by a lot.  Any idea as to why this is?
0

Author Commented:
I didn't notice the final multiplication.  That's what was setting the figures off.
You had it right.  Thanks so much!
0

## Featured Post

• 12
• 10
Tackle projects and never again get stuck behind a technical roadblock.