Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 63
  • Last Modified:

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

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
hermesalpha
Asked:
hermesalpha
  • 12
  • 6
  • 2
2 Solutions
 
ProfessorJimJamCommented:
use this

=(Index(Travbanor!A6:A100;Match(C1;Travbanor!B6:B100;0)))*-1
0
 
hermesalphaAuthor Commented:
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
 
hermesalphaAuthor Commented:
Also, can I have the zero written out if a zero is found?
0
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.

 
ProfessorJimJamCommented:
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
 
Rob HensonIT & Database AssistantCommented:
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
 
hermesalphaAuthor Commented:
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
 
Rob HensonIT & Database AssistantCommented:
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
 
hermesalphaAuthor Commented:
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
 
Rob HensonIT & Database AssistantCommented:
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
 
hermesalphaAuthor Commented:
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
 
Rob HensonIT & Database AssistantCommented:
Can you copy your actual formula here to look at?
0
 
hermesalphaAuthor Commented:
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
 
Rob HensonIT & Database AssistantCommented:
If you put the 0 in quotes it will be text so will still show even with global hiding of zeros.
0
 
hermesalphaAuthor Commented:
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
 
Rob HensonIT & Database AssistantCommented:
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
 
hermesalphaAuthor Commented:
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
 
hermesalphaAuthor Commented:
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
 
hermesalphaAuthor Commented:
No, my second change adding the *-1 in two places did not work, because I do not get the zero displayed then.
0
 
hermesalphaAuthor Commented:
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
 
hermesalphaAuthor Commented:
Thanks for the solution, works fine now.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 12
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now