Help: Excel Formula tidy up

[Walshie1987]

Hi, I have a spreadsheet to do do some calculations at work, to do with prices.

Basically I want to put "If the value = £XX plus or minus £0.01"

At the min here is my formula

=IF(OR(IF(W5="C",ROUNDUP((VLOOKUP(J5,data,3,FALSE)/0.95),2),"")=Z5,(IF(W5="C",ROUNDUP((VLOOKUP(J5,data,3,FALSE)/0.95),2),""))=Z5-0.01,(IF(W5="C",ROUNDUP((VLOOKUP(J5,data,3,FALSE)/0.95),2),""))=Z5+0.01),"Correct","Check")

Any ideas how I could tidy the above up abit? to save me typing out,(IF(W5="C",ROUNDUP((VLOOKUP(J5,data,3,FALSE)/0.95),2),"") over and over again?

Any comment appreciated

Cheers
Chris

 

[SkipVought]

Chris,

"...to save me typing out,(IF(W5="C",ROUNDUP((VLOOKUP(J5,data,3,FALSE)/0.95),2),"") over and over again..."

You DO know about copying a formula and pasting it to propogate the cell references? You should NEVER have to do formulas, "over and over again!!!"

Does W5 remain ABSOLUTE? Of so, check out the F4 key -- Select a reference in your formula and hit the F4 key to observe what happens.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[GlennUK]

How about:

=IF(W5="C",IF(ABS(ROUNDUP((VLOOKUP(J5,data,3,FALSE)/0.95),2)-Z5)<=0.01,"Correct","Check"),"")

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Walshie1987]

Cheers Glenn!! Worked perfect.

.....Yeah skip I do know about copy and paste, was looking for more of an answer like Glenns though

Cheers
Chris

 

[SkipVought]

So what did this question mean?

"Any ideas how I could tidy the above up abit? to save me typing out,(IF(W5="C",ROUNDUP((VLOOKUP(J5,data,3,FALSE)/0.95),2),"") over and over again?"


Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[GlennUK]

Hi Skip,

I think Chris meant that the internal bit was being repeated to compare against zero, and used again to compare against -0.01, and being used yet again to compare against +0.01.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SkipVought]

"...typing out,.... over and over again..."

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[GlennUK]

Yes, that's misleading. I never type anything over and over and over and over and over and over and over and over and over and over and over and over and over and over and over and over and over and over and over and over and over and over and over and over and over and over and over and over and over and over and over and over and over and over and over and over. Gee that copy and paste action is useful.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.