Excel Help

[alphaomega16]

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.

 

[ilovedvds]

Can you do it with a custom fill series ?

Fill data automatically in worksheet cells - Excel

 

[alphaomega16]

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.

 

[ilovedvds]

Ok what about the Power function ?

POWER function - Excel

 

[kBm]

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]

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.

 

[Deleted member 30535]

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.

 

[kBm]

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.

No, its the exact terms. I was using the search function from a quick google search. If there is a easier/more efficient function, i'll happy use it.

 

[Deleted member 30535]

If that's the case, then DP's solution will work.

 

[DPinBucks]

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]

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?

 

[SteakAndCake]

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?

 

[DPinBucks]

... Can you combine IF and DATE functions?

Not directly, but you could do something like

=IF(YEAR([Cell]<1910,"",DATE(YEAR([Cell], etc)

 

[andyparksy]

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!