• New Patreon Tier and Early Access Content available. If you would like to support AVForums, we now have a new Patreon Tier which gives you access to selected news, reviews and articles before they are available to the public. Read more.

Excel Help

alphaomega16

Distinguished Member
Heya chaps, been a LONG time since I used this program and pretty much forgot everything I knew lol.

I want to do a chart that doubles, then triples and so on like this

1000 < Original Number
2000 < Doubled
3000 < Tripled

and so on, I thought it started with doing =A2*2 then try and do something with it but I am at a complete loss.
 

alphaomega16

Distinguished Member
Tried using that but instead of doubling the first then tripling the second its just doubling the one above it.

Edit

Sorted it, I was trying to make it more complicated that it is.
 

kBm

Distinguished Member
Any one fancy helping on this simple one?

I need a formula; if the cell contains "Low" -return 1, if the cell contains "Med" - return 3, if the cell contains "High" - return 5.

Problem is i can only get true / false for 2 our of the 3 values.

This is what im using (without sucess);
=IF(OR(ISNUMBER(SEARCH("LOW",N4)),ISNUMBER(SEARCH("MED",N4)),ISNUMBER(SEARCH("HIGH",N4),"5","3","1")

Cheers.
 

DPinBucks

Distinguished Member
Any one fancy helping on this simple one?

I need a formula; if the cell contains "Low" -return 1, if the cell contains "Med" - return 3, if the cell contains "High" - return 5.

Problem is i can only get true / false for 2 our of the 3 values.

This is what im using (without sucess);
=IF(OR(ISNUMBER(SEARCH("LOW",N4)),ISNUMBER(SEARCH("MED",N4)),ISNUMBER(SEARCH("HIGH",N4),"5","3","1")

Cheers.
This works if [Cell] can only contain "Low", "Med" or "High":

=IF([Cell]="Low", 1, IF([Cell]="Med", 3, 5))

It defaults to 5 if [Cell] doesn't contain "Low" or "Med".

If it can contain anything, but you're only interested in the three values, then use:

=IF([Cell]="Low", 1, IF([Cell]="Med", 3, IF([Cell]="High", 5, "")))

The "" is the default return if it's not one of the three required values.
 
D

Deleted member 30535

Guest
This works if [Cell] can only contain "Low", "Med" or "High":

=IF([Cell]="Low", 1, IF([Cell]="Med", 3, 5))

It defaults to 5 if [Cell] doesn't contain "Low" or "Med".

If it can contain anything, but you're only interested in the three values, then use:

=IF([Cell]="Low", 1, IF([Cell]="Med", 3, IF([Cell]="High", 5, "")))

The "" is the default return if it's not one of the three required values.

What if the values of "Low" "Med" and "HigH" occur as part of a (longer) string? I think that's why he was using the search function.
 

DPinBucks

Distinguished Member
What if the values of "Low" "Med" and "HigH" occur as part of a (longer) string? I think that's why he was using the search function.
Oh, OK. I didn't think of that. Then replace [Cell]="Low", etc, with

NOT(ISERR(SEARCH("Low", [Cell], 1)))

=== EDIT ===
Overtaken by events. Still, I'll leave it there for info.
 

kBm

Distinguished Member
Another quick one?

Now that i have got my cell value as either 1, 3, or 5, (to represent number of years) i've used a simple DATE function to add the value to the date i enter (in this case cell M4);

=DATE(YEAR(M4)+J4,MONTH(M4),DAY(M4))

So cell J4 either adds 1, 3, or 5 years to the date. Happy days.

How do i tell Excel to return a blank cell ("") if no date is entered as it defaults to the 1900 date? As i work around i can set conditional format to white font if the cell contains the serial numbers of the date value, which is then over ridden when i enter a date i want, but there must be a better way. Can you combine IF and DATE functions?
 
Another quick one?

Now that i have got my cell value as either 1, 3, or 5, (to represent number of years) i've used a simple DATE function to add the value to the date i enter (in this case cell M4);



So cell J4 either adds 1, 3, or 5 years to the date. Happy days.

How do i tell Excel to return a blank cell ("") if no date is entered as it defaults to the 1900 date? As i work around i can set conditional format to white font if the cell contains the serial numbers of the date value, which is then over ridden when i enter a date i want, but there must be a better way. Can you combine IF and DATE functions?


What about the IsBlank function?
 

andyparksy

Prominent Member
Would use the isblank one as stated above..... And if you are doing it properly "" is not a blank cell, it's a zero length txt cell that can cause problems if you then use it to do other functions.... Just leave a space after the , for a true blank cell, such as if(j8=6, "true",) notice nothing after the the last comma.

Sorry, pedantic a know but a very very good habit to get into ad using "" causes loads of problems!
 

The latest video from AVForums

SVS Prime Wireless Pro Powered Speakers - Review Coming Soon
Subscribe to our YouTube channel

Full fat HDMI teeshirts

Support AVForums with Patreon

Top Bottom