excel formula

[incisor]

Hi

i am wanting to automate calculations in an excel spreadsheet but i am not getting it right :p

i wish to have a formula so that if cell D14 has a value greater than 0 then use this formula to put a value in this cell

what i have is

=IF(D14>=0,((D14*10.75)+5.5))

and it always show 5.50 if the value is 0

what am i doing wrong....

please

[biggrin]

[grey_ghost]

Hi Inc,

I've read your post a few times - and I'm still not sure I understand your question! [bighmmm]

Are you saying that if the value is 0 - then you don't want it to display anything at all?

If you change the >=0 to just >0 then if the value is zero is says "False"...

I think that I'm missing something.. Sorry.

[incisor]

That's correct.. if cell value is 0 then don't bother with formula...

If cell value is bigger than 0 apply formula

[grey_ghost]

Have you tried using the IF NOT expression?

In English - if NOT D14 =0 then do calc??

[grey_ghost]

=IF(NOT(D14=0),(D14*10.75)+5.5,"")

Using the above doesn't display the word "False" - but I don't think that's answering your question either?!?

[Lee Jackson]

=IF(D14>=0,((D14*10.75)+5.5))


The problem incisor is if D14 = 0 then it meets if 0>=0 so it will calculate (0*10.75) + 5.5 which = 5.5

With not knowing what you are trying to achieve I assume you want to use >. Instead of >=

:)

Hope this helps

[Ferret]

That's correct.. if cell value is 0 then don't bother with formula...

If cell value is bigger than 0 apply formula

=IF(D14>0,((D14*10.75)+5.5),"")

[p38arover]

what i have is

=IF(D14>=0,((D14*10.75)+5.5))

and it always show 5.50 if the value is 0

Two things.

Firstly, it's looking to see if D14 is greater than or equal to zero so it will always be true unless D14 is less than zero.

Secondly, there is a missing argument to the logical test: IF(logical_test,[value_if_true],[value_if_false])

Try =IF(D14>0,((D14*10.75)+5.5),0)

Note the change of >= to just > and the ,0 at the end.

[incisor]

Note the change of >= to just > and the ,0 at the end.

thanks Ron that did it exactly!

thanks for the help people, was much appreciated!

[incisor]

=IF(D14>0,((D14*10.75)+5.5),"")

this left the cell empty, negating my formatting as currency..

will come in handy elsewhere!

thank you...

[Lionelgee]

Hello All Excel Spreadsheet Formula Users,

Just to keep Incisor's Excel thread running.

Is there a What-If analysis, or some similar function, that can isolate the last four numbers of a column that should have featured in a spreadsheet as a separate column that identifies a post code. For example - find examples where the post code 4670 appears in a 70 page spreadsheet.

Here is a sample of what the author wrote Column "G" as:

23 Isaac Street, Deeragun, QLD 4818
6 Narabang Way, Belrose, NSW 2085
60 Margaret Street, Level 24, Sydney, NSW 2000
47 Bourbong Street, Bundaberg, QLD 4670
55 Fulham Road, Pimlico, QLD 4812
106 Cunningham Street, Dalby, QLD 4405
43 Gregory Street, Mackay, QLD 4740
78 Beischer Street, Bendigo, VIC 3550
Po Box 4546, Kirwan, QLD 4817

How could I write a formula that sorts the 4670 entries and their related data in the same row cells out for 4670?

The technique I have been using to date has been highlight column G and perform a Find function to identify each occurrence of 4670. This is a laborious task that will not get any easier as the file is updated every now and then.

I tried a "Custom Sort and Filter" however, the mixture of numbers and letters in the cell prevented it. The author is also inconsistent with their use of commas so "Comma delineation" does not work either.

I would appreciate assistance - thank you.

Kind Regards
Lionel

[Tombie]

Lionel. Can you make another column? (H)?

[shanegtr]

Lionel. Can you make another column? (H)?
That would work, create a helper colum next to your data: Use the formula RIGHT and specifiy 4 characters - this will extract the last 4 digits from the right (providing that the last 4 digits are always a postcode:
I just copied one of your address's and pasted into cell B3, in cell C3 I entered the forumula =RIGHT(B3,4). That will pull the postcode into your helper column
129744

[Lionelgee]

Hello Shane,

Well that worked a treat Shane [bigsmile1]. Thank you very much for sharing your knowledge. =RIGHT(G3,4)

I also just found how to copy this formula from Cell H3 to H4953 (H3:H4953); and then go Control + D to fill the whole column with post codes.
I could then do a Custom Filter for 4670 and get rid of 69 pages of detritus :banana::banana::banana:

It is a shame that a certain government department could not just expend a little more effort and have the last two columns labelled "Suburb" and "Post Code" so that no one had to do =RIGHT(G3,4) in the first place!

However, that would be just too damn user-friendly! :twisted:

Kind Regards
Lionel

[Tombie]

Glad that sorted it out Lionel..

I live in an excel space!!

Yes it’s one spread sheet, yes it’s 60+ tabs, over 2000 lines per tab and all interconnected!

https://www.aulro.com/afvb/images/imported/2017/09/604.jpg

[Disco-tastic]

Glad that sorted it out Lionel..

I live in an excel space!!

Yes it’s one spread sheet, yes it’s 60+ tabs, over 2000 lines per tab and all interconnected!

https://www.aulro.com/afvb/images/imported/2017/09/604.jpgHoly. Crap. That thing is monstrous!

[theresanothersteve]

Glad that sorted it out Lionel..

I live in an excel space!!

Yes it’s one spread sheet, yes it’s 60+ tabs, over 2000 lines per tab and all interconnected!


Toombie, I'm an Excel nerd, but I've got to ask, have you heard of Access?

I was thinking Access for the OP, and he's only got 5,000 records!

[Tombie]

Toombie, I'm an Excel nerd, but I've got to ask, have you heard of Access?

I was thinking Access for the OP, and he's only got 5,000 records!

That spreadsheet was fed from several ADbs [emoji6]

But I hear what you’re saying!

The Challenge was the ever changing outputs and inputs.

Although Nowadays I play in a much broader space, from Excel reports to Isolation Systems and Confined Space Rescue Plans, through to hands on construction, trouble shooting and commissioning of new plants....

[Strop]

Glad that sorted it out Lionel..

I live in an excel space!!

Yes it’s one spread sheet, yes it’s 60+ tabs, over 2000 lines per tab and all interconnected!

https://www.aulro.com/afvb/images/imported/2017/09/604.jpg

I can’t see the bottom left corner but do you have coffee for each monitor?

[VladTepes]

When people come to you assign for information,. can you say

"Don't bother me, read it for yourself ! It's right there plain as day in cell 1985674 GZ" ?




Anyway there is only one formula that anyone here needs to know.

The number of Land Rovers you NEED is n=n+1 (where 'n' is the number you already have)

[Tombie]

I can’t see the bottom left corner but do you have coffee for each monitor?

Iced Coffee [emoji41]

[theresanothersteve]

Anyway there is only one formula that anyone here needs to know.

The number of Land Rovers you NEED is n=n+1 (where 'n' is the number you already have)

The trouble with that formula is you only need another once you've bought another.

I would prefer


Do While LandRoversOwned < LandRoversWanted
LandRoversWanted = LandRoversOwned + 1
DoEvents
Loop

That way you never have enough...

[Tombie]

Don’t forget the Sub HASWIFEFOUNDOUT..