?
Solved

How do I get the retrieved value changed automatically to either a plus or a minus in Excel 2007?

Posted on 2016-09-30
20
Medium Priority
?
57 Views
Last Modified: 2016-11-07
I have this formula for retrieving a value:

=Index(Travbanor!A6:A100;Match(C1;Travbanor!B6:B100;0))

The retrieve value might be a minus value, a 0 or a plus value. If a minus value or a plus value the minus/plus should be reverted to the opposite, if a zero nothing should happen.
0
Comment
Question by:hermesalpha
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 6
  • 2
20 Comments
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 41823374
use this

=(Index(Travbanor!A6:A100;Match(C1;Travbanor!B6:B100;0)))*-1
0
 

Author Comment

by:hermesalpha
ID: 41823441
I use a vlookup function instead, so I wonder in which position I should place your *-1 in this formula?:

=IFERROR(VLOOKUP(C12;Travbanor!B6:L100;11;FALSE);"")
0
 

Author Comment

by:hermesalpha
ID: 41823443
Also, can I have the zero written out if a zero is found?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 340 total points
ID: 41823449
wrap the entire formula inside to parenthesis and then multiply by minus one -1

like this =(IFERROR(VLOOKUP(C12;Travbanor!B6:L100;11;FALSE);""))*-1
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41823458
I would say the *-1 has to be within the VLOOKUP or you risk multiplying "" by -1.

=(IFERROR(VLOOKUP(C12;Travbanor!B6:L100;11;FALSE)*-1;""))

Or put the minus in front of the VLOOKUP:

=IFERROR(-VLOOKUP(C12;Travbanor!B6:L100;11;FALSE);"")

Minusing a negative number gives positive, and vice versa. Minus 0 is still zero.
0
 

Author Comment

by:hermesalpha
ID: 41823666
Thanks, that worked when I put the *-1 within the VLOOKUP.

Is there any way to format this cell only so it always displays a zero?
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41823681
To display 0 no matter the actual value is possible with custom format:

"0";"0";"0";"0"

But why would you want to do that?
0
 

Author Comment

by:hermesalpha
ID: 41823687
I mean, if there is no minus value and no plus value to retrieve but there is a "0" (not empty cell, but "0" is written in it), then I want this value also to be retrieved (not only minus or plus value to be retrieved).
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41823698
If the VLOOKUP result is zero or blank then the result of the formula will be zero. The IFERROR part of above formula will put blank when the lookup value is not found. If you want zero in that instance change the "" at the end to 0
0
 

Author Comment

by:hermesalpha
ID: 41866535
I tried now adding a "0" at the end (where the "" where). But nothing is displayed in the cell when the value to retrieve is "0".
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41866668
Can you copy your actual formula here to look at?
0
 

Author Comment

by:hermesalpha
ID: 41866670
This is my current formula:

=(IFERROR(VLOOKUP(C12;Travbanor!B6:L100;11;FALSE)*-1;0))

I don't want to turn on global displaying of zeros as I'm not sure the zeros should be displayed everywhere. Is there any way to format a single cell to display zeros?
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41866679
If you put the 0 in quotes it will be text so will still show even with global hiding of zeros.
0
 

Author Comment

by:hermesalpha
ID: 41867149
I tried now, but same as before, no zero is displayed:

=(IFERROR(VLOOKUP(C12;Travbanor!B6:L100;11;FALSE)*-1;"0"))

But what if I delete the "IFERROR"?
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 1660 total points
ID: 41868354
The IFERROR covers the scenario when value in C12 is not found in range Travbanor!B6:B100 (first column of lookup range). Without the IFERROR you will get #N/A error.

If the value in C12 is found and the result from column 11 is blank or zero then the result will be zero which will be hidden by the suppression of zeros. If you still want these zeros to show then you will have to allow for it in the formula:

=IFERROR(IF(VLOOKUP(C12;Travbanor!B6:L100;11;FALSE)=0;"0";VLOOKUP(C12;Travbanor!B6:L100;11;FALSE));"0")

This says if the result of the lookup is zero put "0" otherwise put the result; if the result is an error then put "0".
0
 

Author Comment

by:hermesalpha
ID: 41876913
It almost worked now, zero is dislayed when it should. But I need to put the *-1 somewhere to have the minus/plus switched.
0
 

Author Comment

by:hermesalpha
ID: 41876916
This worked, now I get the minus/plus switched also:

=IFERROR(IF(VLOOKUP(C12;Travbanor!B6:L100;11;FALSE)*-1=0;"0";VLOOKUP(C12;Travbanor!B6:L100;11;FALSE))*-1;"0")
0
 

Author Comment

by:hermesalpha
ID: 41876917
No, my second change adding the *-1 in two places did not work, because I do not get the zero displayed then.
0
 

Author Comment

by:hermesalpha
ID: 41876931
This worked, now I get zero displayed and also switched minus/plus:

=IFERROR(IF(VLOOKUP(C12;Travbanor!B6:L100;11;FALSE)=0;"0";VLOOKUP(C12;Travbanor!B6:L100;11;FALSE)*-1);"0")
0
 

Author Closing Comment

by:hermesalpha
ID: 41876936
Thanks for the solution, works fine now.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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…

801 members asked questions and received personalized solutions in the past 7 days.

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

Join & Ask a Question