Solved

Posted on 2011-10-10

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

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

16 Comments

=if(G82=4000,G81,if(G82=60

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.

Please try the following...

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

Regards,Brian.

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

=IF(G82<4000,G81,IF(G82<60

Saurabh

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

IF(G82>10000,G81-2500

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

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

=G81-VLOOKUP(G82,K82:L86,2

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

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

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

Title | # Comments | Views | Activity |
---|---|---|---|

Populate a word template from Excel | 5 | 24 | |

Third Sunday of the Month | 10 | 50 | |

add a password to open excel after 60 days | 8 | 27 | |

exporting issues in Access | 2 | 17 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**24** Experts available now in Live!