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

x
Solved

# Excel 2003 - cell value based on the another's value

Posted on 2011-10-10
Medium Priority
804 Views
I have an Excel 2003 worksheet that depending what is put in one cell another cells value is calcualted.

If G82 is 4000 then G83 is G81

If G82 is 6000 then G83 is G81 - 1500
if G82 is 10000 then G83 is G81 - 2500
if G82 is 80000 then G83 is null
0
Question by:Pdeters
• 5
• 3
• 3
• +4

LVL 5

Expert Comment

ID: 36943557
Use VLOOKUP Command that will help.
0

LVL 11

Expert Comment

ID: 36943567
in G83 put in:
=if(G82=4000,G81,if(G82=6000,G81-1500,if(G82=10000,G81-2500,if(G82=80000,null,"other G82 values"))))

of course you can decide what the value should be if G82 isn't any of the expected value.  I just put "other G82 values" as an example.
0

LVL 26

Expert Comment

ID: 36943569
Hi, Pdeters.

``````=IF(G82=4000,G81,IF(G82=6000,G81-1500,IF(G82=10000,G81-2500,IF(G82=80000,"","Some Other Value"))))
``````
Regards,
Brian.
0

Author Comment

ID: 36943622
Sorry - not = to but >
0

Author Comment

ID: 36943657

If G82 < 4000 then G83 is G81

If G82 >3999 and less than  6000 then G83 is G81 - 1500
if G82 >5999 and less than 10000 then G83 is G81 - 2500
if G82 is 80000 then G83 is null
0

LVL 24

Accepted Solution

StephenJR earned 1600 total points
ID: 36943687
Try this. What if G82 is >10000 but not 80000?

=IF(G82<4000,G81,IF(G82<6000,G81-1500,IF(G82<10000,G81-2500,IF(G82=80000,"Null"))))
0

LVL 59

Expert Comment

ID: 36943689
One quick question.. what will be g82 value if the value is between 10000 and 80000 because you havent specified that value...

Saurabh
0

Author Comment

ID: 36943696
i didn't put them all in becasue If i can get partial i shuld be able to do the rest?
0

LVL 26

Expert Comment

ID: 36943707
Pdeters,

``````=IF(G82>80000,"",IF(G82>10000,G81-2500,IF(G82>6000,G81-1500,IF(G82>4000,G81,"Some Other Value"))))
``````
0

Author Comment

ID: 36943765
Got it to work but how do I have it show 0 if a negative amount shows?
0

LVL 11

Expert Comment

ID: 36943781
Do another IF around it:  IF(...>=0, ..., 0).   "..." is the working formula you have.
0

LVL 26

Expert Comment

ID: 36943805
=IF(G82>80000,"",IF(G82<4001,"Some Other Value",ABS(IF(G82>10000,E81-2500,IF(G82>6000,E81-1500,E81)))))
0

Author Comment

ID: 36943807
? Not sure how to put anothe rIf aournd -

IF(G82>10000,G81-2500

if this would = -900 then I want o to show
0

LVL 59

Assisted Solution

Saurabh Singh Teotia earned 400 total points
ID: 36943808

Nah a simpler way to it will be.. this..

=MIN ( Your if formula here, 0)

Saurabh...
0

LVL 81

Expert Comment

ID: 36943861
<<i didn't put them all in becasue If i can get partial i shuld be able to do the rest?>>

For this reason, you really ought to be using a lookup table as Prashantmona suggested at the outset.

=G81-VLOOKUP(G82,K82:L86,2,TRUE)            The lookup table is in K82:L86 in my sample workbook

I assumed that you would be using brackets. If G82 is >= 4000 and < 6000, then G83 = G81 - 1500
You set these brackets up by listing the lowest value in the bracket in the first column, and the value to subtract in the second column. Use as many rows as you like in the lookup table.

You will run into a limit of 7 nested functions if you have too many conditions. The lookup table is a way around that issue.
SubtractionUsingLookupQ27389034.xls
0

LVL 11

Expert Comment

ID: 36944383
This is what I meant by wrapping another IF around:

=if(if(G82=4000,G81,if(G82=6000,G81-1500,if(G82=10000,G81-2500,if(G82=80000,"","other G82 values"))))>0, if(G82=4000,G81,if(G82=6000,G81-1500,if(G82=10000,G81-2500,if(G82=80000,"","other G82 values")))),0)

Or use the solution suggested by the other comment (use Max() though)

=Max(if(G82=4000,G81,if(G82=6000,G81-1500,if(G82=10000,G81-2500,if(G82=80000,"","other G82 values"))))>0,0)

Should be fine with the current conditions, but if you have more conditions, a lookup table could be a more manageable solution.

0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
###### Suggested Courses
Course of the Month19 days, 17 hours left to enroll